Append MS SQL table from MS Access table

[goingkraze]

I have been asked to write a piece of code to take a local MS Access table and bulk load to the MS SQL table. I have searched for threads, but just can't figure out how to get a SQL statement to recognize the local table.

I have this so far
MyCon.Execute ("INSERT INTO WAREHOUSE.dbo.TABLE1 SELECT * FROM Table1;")

but it does not recognize the MS Access table "Table1"

Any help is greatly appreciated.

Thanks

 

[AlexCuse]

I think you will find this easier if you just set up a linked table, or set up a DTS to pull from Access into SQL (rather than push data out). The reason your query is not working, is because it looks for Table1 in the SQL Server Database defined in your connection string.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[goingkraze]

I used the link table to refresh. It is a little slow, but this is only a interim solution until I can get the formal DTS in place.

Thanks AlexCuse for you help.

 

[AlexCuse]

DTS is the best way to do it (IMO of course). I am glad you are pursuing that route

Ignorance of certain subjects is a great part of wisdom