Inserting between databases

[John]

Hi

Can an insert query that inserts values from one table into another take one
of its tables from an sql server database? The sql server is accessible
either via domain or ip address. I prefer not to link the sql server tables
as linked tables raise issues form time to time and require re-linking.

Thanks

Regards

 

[Brent Spaulding \(datAdrenaline\)]

Yes ....

You can use a little know syntax to specify (ie: select records from) a SQL
Server table.... generically, it looks like this:

SELECT * FROM <ODBC Connection String>.tblSomeTable

An actual example (with private info masked, and line breaks added for
clarity):
SELECT *
FROM [ODBC;
DRIVER=SQL Server;
SERVER=xxxxxx;
UID=xxxxxx;
PWD=xxxxxx;
DATABASE=xxxxxx;
Network=DBMSSOCN].tblAreas

You can also use the IN key word (again, line breaks added for clarity) ...

SELECT *
FROM tblAreas
IN [ODBC;
DRIVER=SQL Server;
SERVER=xxxxxx;
UID=xxxxxx;
PWD=xxxxxx;
DATABASE=xxxxxx;
Network=DBMSSOCN]

.....

If you wish to INSERT data from a table in SQL Server INTO an Access table
.... you can do something like this (note that I abbreviated the ODBC connect
string and an assumption of all fields being the same name)

INSERT INTO tblYourAccessTable
SELECT * FROM [ODBC;...].tblYourSQLServerTable

 

[Armen Stein]

Hi

Can an insert query that inserts values from one table into another take one
of its tables from an sql server database? The sql server is accessible
either via domain or ip address. I prefer not to link the sql server tables
as linked tables raise issues form time to time and require re-linking.

Thanks

Regards

A passthrough query can retrieve a value from a SQL Server table
provided you have all the necessary credentials in the connection
string. This passthrough technique doesn't require any linked tables.
You can use SQL or Windows authentication, but with SQL authentication
you run a risk of exposing the credentials by storing them in the
Access database.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com