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

Yes, this is a limitation of using DAO. It's like when you go into Design View of a linked table, you get a message that it will be read-only.
You have to use an ADO connection to run data definition sql statements. And you need a refrenece to "Microsoft ADO x.x for DDL and Security". Then your ADO connection object can do a cnADO.execute "Alter table .....". Provided of course that the user id that you use on the connection has sufficient rights.


Steve


________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Fri, 19 Sep 2014 11:42:43 -0700
> 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<mailto:MS_Access_Professionals@yahoogroups.com>
>> From:
> MS_Access_Professionals@yahoogroups.com<mailto: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<mailto:StephenMConklin@hotmail.com>
> [MS_Access_Professionals]"
>>
> <MS_Access_Professionals@yahoogroups.com<mailto: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><mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
>>> From:
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com><mailto: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><mailto: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><mailto: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><mailto:MS_Access_Professionals-unsubscribe@yahoogroups.com<mailto:MS_Access_Professionals-unsubscribe@yahoogroups.com>>
>
>>
>>
>>
>>
>>
>>
>
> ------------------------------------
>
> ------------------------------------
>
>
> ------------------------------------
>
> 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>
>
>
>
>
>

------------------------------------

------------------------------------


------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/