Insert/Update SQLDB from MSAccess DB

Question

question

Souchie asked Mar 16, '11

Insert/Update SQLDB from MSAccess DB

Hi Experts, I am using a MSAcess 2000 DB to hold temparory data prior to inserting and or updating data in various SQLSErver db's, my problem is getting the syntax right to identify the source as my Access DB, I'm using pass-through queries, my coding works if I use them to delete data from the destination but not update, the SQL string I'm using is: INSERT INTO Person (ColumnA, ColumnB,....ETC) ( SELECT TemplatePerson.ColumnA, TTemplatePerson.ColumnB, TemplatePerson.Column....ETC FROM TemplatePerson IN NewDB.mdb ) the syntax error is the standard incorrect syntax near the Keyword 'IN' looking in books on line this is all thats needed to reference an external MSAccess DB, I am using MSAccess 2000, SQLServer 2005 Express and SQLServer2000 All help is vey much appreciated Thanks for your time Martin

update microsoft-access

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Souchie commented · Mar 16, 2011 at 04:23 AM

I should prehaps also mention that the SQLServer DBs are linked to the Access DB

0 ·

Answer 1 · 2011-03-21T08:22:08Z

ThomasRushton answered Mar 21, '11

Your comment > "I should perhaps also mention that > the SQL Server DBs are linked to the > Access DB" ... does this mean that you are actually running the query within SQL Server, and are attempting to query the Access database from within SQL Server? If so, then the general steps you will need to follow are: 1. Install MS Access ODBC drivers on the same machine as the SQL Server instance 2. Ensure that the SQL Server service user is able to see the Access database file and has the right Windows permissions to update the database 3. Create a [linked server][1] within SQL Server to the Access database 4. Rewrite your query to use the new linked server, eg `SELECT * FROM LinkedAccessDB...TableName` [1]: http://msdn.microsoft.com/en-us/library/ms175866.aspx3.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Mar 21, 2011 at 08:24 AM

@Souchie Sorry for the delay in getting back to you!

0 ·

Souchie commented · Mar 22, 2011 at 12:13 AM

Thank you I was missing the the linked server now works great

0 ·

Answer 2 · 2011-03-16T04:08:08Z

ThomasRushton answered Mar 16, '11

The "IN" keyword is a valid SQL Server keyword. So, if your query is being passed straight through to SQL then that will cause it problems. A possible solution to your problem is explained here: http://bytes.com/topic/access/answers/194233-sql-server-pass-through-query

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Souchie commented · Mar 16, 2011 at 04:12 AM

The statement is exactly as above, however if I try to run it directly from Management studio I get the same synatx error

0 ·

ThomasRushton ♦♦ commented · Mar 16, 2011 at 04:14 AM

Yup, you will, because the "IN" in SQL Server is a special comparison operator, used to check if a value is within a range, eg: SELECT * FROM sysdatabases WHERE name in ('master', 'model', 'msdb', 'tempdb')

0 ·

question