using msde and sqlserver with access front-end
I originally posted this in ASP forum - probably better here ...
I have an Access front end to an msde db on the pc, & wanting to access a sqlserver db on the network.
If I setup the connection to the msde db (File>Connection) then that is the currentproject.connection, & I can reference tables directly.
To get the sqlserver db I create an adodb.connection:
Set mConn = New ADODB.Connection
strConn = "Provider=Microsoft OLE DB Provider for Sql Server;" & _
"Data Source=Epicor;Initial Catalog=TestRig;" & _
"Persist Security Info=False; ;Integrated Security=SSPI;"
& that is fine.
Then I can create a recordset from that db:
rst.Open strSql, mConn, adOpenStatic, adLockReadOnly
How can I INSERT INTO the sql server db ?
I see 3 possible options (none of which I've been able to get working):
1. Is there a syntax for prefixing the table name with server & database ?
2. Can I retain connection to the msde db & link to the sqlserver tables ? If so, can that be done in vba ?
3. My requirement is either to read from the msde db & write to the sqlserver db or vice versa.
Is it possible, in vba, to change the db that is the currentproject.connection ?