Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58

    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;"
    cnn.Open strCnn
    rst.Open strSql, cnn, adOpenStatic, adLockReadOnly

    To insert into the MSDE db I use:

    strSql = "INSERT INTO <tablename> VALUES (...)"
    DoCmd.RunSQL strSql

    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 ?
    catkins

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    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

    S-

  3. #3
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58
    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 ?
    catkins

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •