Hi all

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;"
    mConn.Open strConn
 & 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 ?