var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Referencing tables in another DB
I have an Access front end to a local MSDE db, & want to be able to select from & insert into a Sql Server db on the server.
I have set up the Access connection to the local MSDE db via File>Connection, thus this is the Currentproject.Connection.
In vba I can create a connection to the server db, & can use this to create a recordset from it:
Set cnn = New ADODB.Connection
strCnn = "Provider=Microsoft OLE DB Provider for Sql Server;" & _
"Data Source=Epicor;Initial Catalog=TestRig;" & _
"Persist Security Info=False; ;Integrated Security=SSPI;"
rst.Open strSql, cnn, adOpenStatic, adLockReadOnly
To insert into the MSDE db I use:
strSql = "INSERT INTO <tablename> VALUES (...)"
How do I specify this for the server db ?
ie is there a syntax for specifying the provider & database with the table name for OLE DB connections ?
You are probably not going to be able to do this with a SQl statement.
Open the the records from the MSDE (going through them one at a time) and Add them to the Sql Server (One at a Time)
Your other option would be to link the SQL server to the FE DB and have access act like both DBs are one (tables frm both DB are now part of the currentproject
Well, that sounds ok, I can easily live with reading from msde & writing to sqlserver one record at a time - but what is the syntax for writing to sqlserver ? Can I prefix the table name with server/database ?
Or, the linking option sounds a good way to go. Can that be done from within vba ?