How to link Access table to SQL Server with VBA

Question

I am trying to create a linked (or imported) table in Access with a SQL Server backend. Basically the business users periodically need a copy of the table [SQL Rulesnew] (yes, with the space, sigh) so we want to give them a little Access 2003 tool that will do the job on demand.

I did try using Docmd.TransferDataBase acTable but no luck

here is the code I am using:

Sub getData()

Dim sConnStr As String
Dim oTable As TableDef
Dim sDestinationTable As String
Dim dbs As Database
Dim tbl As DAO.TableDef
Dim tblLinked As DAO.TableDef       

    sDestinationTable = "SQL Rulesnew"
    Set dbs = CurrentDb

    ' source table name has a SPACE (rolleyes!)
    CurrentDb.CreateTableDef sDestinationTable  

    ' got the below from a Data Link File (UDL)
    sConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MYDBNAME;Data Source=MYSERVERNAME"

    ' the below also failed!
    'DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=Fos;Database=Hermes_Rep;Trusted_Connection=Yes", acTable, "[Report SQLRulesnew]", "SQLRules" & VBA.Format(Now, "ddmmyyyy")

    'If DCount("*", "MSysObjects", "[Name]='[SQL Rulesnew]' AND [Type] In (1, 4, 6)") > 0 Then
    If IsTable(sDestinationTable) Then
       DoCmd.DeleteObject acTable, sDestinationTable
    End If

    Set tblLinked = dbs.CreateTableDef(sDestinationTable)
    Debug.Print "Linking the " & sDestinationTable
    tblLinked.Connect = sConnStr
    tblLinked.SourceTableName = sDestinationTable
    dbs.TableDefs.Append tblLinked
    tblLinked.RefreshLink

End Sub

Function IsTable(sTblName As String) As Boolean
    'does table exists and work ?
    'note: finding the name in the TableDefs collection is not enough,
    '      since the backend might be invalid or missing
Dim x
    On Error GoTo Coventry
    x = DCount("*", sTblName)
    IsTable = True
    Exit Function

Coventry:
    Debug.Print Now, sTblName, Err.Number, Err.Description
    IsTable = False
End Function

unfortunately I get an error could not find installable ISAM on the line dbs.TableDefs.Append tblLinked

what should I do?

thanks Philip

Do you already have a linked SQL Server table? If so, check the connect property of the tabledef for a working connection string. — Fionnuala, Apr 03 '13 at 09:28

score 6 · Accepted Answer · edited Aug 16 '13 at 07:44

6

I found the answer through trial and error...

Basically my table names in Access can have a space without using the [square brackets], so the below command works fine (after deleting any existing object):

DoCmd.TransferDatabase _
  acImport, _
  "ODBC Database", _
  "ODBC;Driver={SQL Server};Server=Fos;Database=Hermes;Trusted_Connection=Yes", _
  acTable, _
  "sourceTable", _
  "targetTable"

edited Aug 16 '13 at 07:44

SteveC

15,808
23
102
173

answered Apr 03 '13 at 09:44

Our Man in Bananas

5,809
21
91
148

1

sometimes, while running non-english windows you might get error 2507, change "ODBC Database" for "ODBC" (works with French). (Thanks to tony gil: http://stackoverflow.com/questions/5858366/fastest-secure-way-for-export-mysql-tables-into-ms-access-table) – Lionel T. Oct 26 '15 at 23:55
This doesn't work real good if you're trying to automate it, try this instead: http://stackoverflow.com/a/38266765/18149 – leeand00 Jul 08 '16 at 13:38

How to link Access table to SQL Server with VBA

1 Answers1