ACCESS FEED: RE: [MS_AccessPros] Venturing into upsizing Access to SQL but have an issue

Jumat, 19 September 2014

RE: [MS_AccessPros] Venturing into upsizing Access to SQL but have an issue

I believe you cannot alter an SQL server table as you would in Access which is what the error is saying.

I believe Truncate Table is the command you need to send to SQL Server as a Pass Through Query.

Bob Peterson

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, September 19, 2014 2:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Venturing into upsizing Access to SQL but have an issue

Here is my code below. for which I get the following error

Runtime error 3611

Cannot execute data definition statements on linked data sources

Private Sub Form_Open(Cancel As Integer)
Dim rs As New ADODB.Recordset

'this set of code will
    'delete the records in the tblInProcessNewTask
    'alter the NewTaskID to reset to autonumber 1 each time
    'Append the records from the tasks table

    ' Point to this database
    Set db = CurrentDb

    'deletes all records in the tblInProcessNewTask table
    db.Execute "qryDeleteInProcessNewTask", dbFailOnError Or dbSeeChanges
        'tblInProcessNewTask

' Give the engine time to finish and free all locks
DBEngine.Idle dbFreeLocks

    'alter the table to reset the autonumber for the NewTaskID field to 1 each time the form is opened.
    ' Set counter back to 1
    db.Execute "qryAlterInProcessNewTask", dbSeeChanges
        'ALTER TABLE [Copy Of tblInProcessNewTask] ALTER COLUMN NewTaskID COUNTER (1, 1);

   'append the tblInProcessNewTask table with the in process records.
    db.Execute "qryAppendtblInProcessTasks", dbFailOnError
        'tblTasks           'main table with the tasks
        'tblTaskDetails     'related table to the tblTasks. the details for the tblTasks table

    Set db = Nothing

DoCmd.OpenForm "frmTasks"
DoCmd.Close acForm, "frmSplash"
DoCmd.OpenForm "frmInProcessTasksMain"

End Sub

Jim Wagner

On Friday, September 19, 2014 11:30 AM, "Steve Conklin StephenMConklin@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

The dbSeeChanges is a common need when hitting MSSQL tables, as in
CurrentDb.OpenRecordset "select * from tbl", , dbSeeChanges

If you don't see the table in Linked Table Manager, I have to ask, are you sure you deleted the linked table from Access and not the actual table in MSSQL?

Steve

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Fri, 19 Sep 2014 10:48:33 -0700
> Subject: Re: [MS_AccessPros] Venturing into upsizing Access to SQL but
> have an issue
>
>
>
> Steve,
>
> Okay, I refreshed the link and now get another error.
> Run time error 3622 You must use the dbSeeChanges option with
> OpenRecordset when accessing a SQL Server table that has an IDENTITY
> column.
>
> ??? what???
>
>
> So I deleted the table and tried to link the table and I do not see the
> database in the select data source for the Machine Data source. I guess
> the upsize wizard does not create that source or I am missing
> something.
>
>
> Jim Wagner
>
>
> On Friday, September 19, 2014 10:35 AM, "Steve Conklin
> StephenMConklin@hotmail.com [MS_Access_Professionals]"
> <MS_Access_Professionals@yahoogroups.com> wrote:
>
>
> Before deleting from MSSQL, try refreshing the link first. If that
> doesn't work, delete the table from Access and then re-link.
>
> Steve Conklin
>
>
> ________________________________
>
>> To:
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>> From:
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>> Date: Fri, 19 Sep 2014 10:32:08 -0700
>> Subject: [MS_AccessPros] Venturing into upsizing Access to SQL but have
>> an issue
>>
>>
>>
>> Hello all,
>>
>> I just used the upsizing wizard and am now receiving an error about an
>> index. The specific error is Run-time error 3086 could not delete from
>> the specified tables. I click debug and it is highlighting a delete
>> query that runs with the on open event. The table in the query showed
>> up as a error in the upsizing report that could not be updateable. So I
>> went into SMS and added a PK to a field and I still get the error. The
>> table in the Access db shows no PK on the field but does in the SMS. Do
>> I need to delete the database from SMS and start over with the table
>> corrected?
>>
>> Thank you
>>
>>
>> Jim Wagner
>
>>
>>
>
> ------------------------------------
>
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
> MS_Access_Professionals-digest@yahoogroups.com<mailto:MS_Access_Professionals-digest@yahoogroups.com>
>
> MS_Access_Professionals-fullfeatured@yahoogroups.com<mailto:MS_Access_Professionals-fullfeatured@yahoogroups.com>
>
> MS_Access_Professionals-unsubscribe@yahoogroups.com<mailto:MS_Access_Professionals-unsubscribe@yahoogroups.com>