[AccessD] A2003:Importing/Linking SQL Tables (and data)

Darren,

This code works well here:

Public Sub Import()
Dim strCnn As String
    strCnn = "ODBC;DRIVER=SQL Server;" & _
             "SERVER={1};" & _
             "DATABASE={2};" & _
             "Trusted_Connection=Yes;"
    strCnn = Replace(strCnn, "{1}", "(local)") ' server
    strCnn = Replace(strCnn, "{2}", "pubs")    ' database

    DoCmd.TransferDatabase _
          acImport, _
          "ODBC", _
          strCnn, _
          acTable, _
          "authors", _
          "authors"
End Sub

Public Sub Link()
Dim strCnn As String
    strCnn = "ODBC;DRIVER=SQL Server;" & _
             "SERVER={1};" & _
             "DATABASE={2};" & _
             "Trusted_Connection=Yes;"
    strCnn = Replace(strCnn, "{1}", "(local)") ' server
    strCnn = Replace(strCnn, "{2}", "pubs")    ' database

    DoCmd.TransferDatabase _
          acLink, _
          "ODBC", _
          strCnn, _
          acTable, _
          "authors", _
          "linked_authors"
End Sub

Hope it will work for you too.

Shamil


----- Original Message ----- 
From: "Darren DICK" <darrend at nimblesystems.com.au>
To: <accessd at databaseadvisors.com>
Sent: Wednesday, December 14, 2005 3:54 PM
Subject: [AccessD] A2003:Importing/Linking SQL Tables (and data)


>
> Hello all
> When I use something like
>     DoCmd.TransferDatabase acImport, "ODBC", "PhoneCentral", acTable,
> "SQLTABLENAME", "MDBTABLENAME", False
> To import some SQL tables I expected to see the Standard ODBC password
login
> stuff
> Instead I get an error message
> Run Time Error 3170
> Could not fin installable ISAM
>
> Does anyone have some code where I can loop through a recordset of
> tablenames
> And import them from SQL into an AccessdB
> And the same for linking as well?
>
> Many thanks
>
> Darren
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com