0

I have a database that has been working well for a couple years until this morning. When I attempt to copy the contents of a remote table into a local backend, I am presented with an err: "Error 3622 - You must use the dbSeeChanges option..."

The remote table is on a server and does have an AutoNumber attribute. The backend table is a simple readonly/static snapshot that does not care about the auto numbering datatype and is defined simply as Number - I just need the table (snapshot) to be local for performance concerns.

I added the dbSeeChanges variable without success - complains about "Too few Parameters" on the db.execute line (below).

Here are some details from my db:

Dim db As Database
dim strSQL as string
Set db = CurrentDb()

strSQL = "INSERT INTO Item " & _
    "SELECT dbo_Item.* " & _
    "FROM dbo_Item " & _
    "WHERE dbo_Item.MASTER_INVID=  " & TempVars!my_InvID

    db.Execute strSQL, dbFailOnError + dbSeeChanges

What am I missing? Any suggestions to avoid/correct.

Mark Pelletier
  • 1,329
  • 2
  • 24
  • 42
  • I think there is something going on with your SQL string. `Debug.Print` it before executing. See [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) – Andre Nov 17 '16 at 14:25
  • debug.print looks fine: INSERT INTO Item SELECT dbo_Item.* FROM dbo_Item WHERE dbo_Item.MASTER_INVID = 4892 – Mark Pelletier Nov 17 '16 at 14:33
  • 1
    @Andre - you may be onto something there! I was connected to the wrong server where that "Master_InvID" attr was not part of the data model - yet. That attr gets created downstream from where I was connecting. - Thanks! – Mark Pelletier Nov 17 '16 at 15:11

1 Answers1

1

Another way to do this is to make a copy of the linked table then covert that to a local table:

localTableName = "Item"
DoCmd.CopyObject , localTableName , acTable, "dbo_Item"
DoCmd.SelectObject acTable, localTableName , True
RunCommand acCmdConvertLinkedTableToLocal
ClintB
  • 509
  • 3
  • 6
  • That's interesting, I had never heard of `acCmdConvertLinkedTableToLocal`. Does it work for ODBC linked tables too, or only for tables linked from an Access database? – Andre Nov 18 '16 at 07:49
  • Yes it also works from ODBC sources such as SQL servers. – ClintB Nov 19 '16 at 16:55