Insert Into - from Access to SQL

[chequi]

Hello:

I am trying to insert a group of tables from an Access database to a SQL database using the following VB code:

'****************************************
gsdatabase ="C:\data\bkup.mdb"

db.ConnectionString = "DSN=" & connstr 'connstr is the name of a valid SQL ODBC connection
db.open

ssql = "Insert into " & TABLAS(i) & " Select * from " & TABLAS(i) & " In '" & gsdatabase & "'"

db.Execute ssql
'***************************************
I am getting the following error message:

[Microsoft][ODBC SQL Server]Incorrect syntax near the keyword 'In'

What is wrong?. I have used that before but from Access to Access.

Any help will be appreciated.

Chequi.

 

[Golom]

Just a guess but you are running the Execute from an SQL Server database object. I'm not sure that SQL server supports the From tbl IN 'Path' syntax. You may try linking the source table into your Access database and then running this without the "IN" clause.

 

[jel]

The syntax "tablename IN database" is Jet-SQL.
Maybe you'll get it working if you use the Jet-driver instead of the SQL-driver.

 

[teniosoft]

Just use access it is great for this.

Link Tables
Run append query
your good to go.

 

[chequi]

Is there a way to link the tables programatically?.
I solved my problem by inserting record by record. I don't want to link all tables by hand; but now I am wondering if I can do the link programatically.

Thanks to all you folks.

Chequi.

 

[Golom]

Sure. Look at this thread

thread181-38038