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
– Fionnuala, Commented Apr 3, 2013 at 9:28

SteveC · Accepted Answer · 2013-08-16 07:44:06Z

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, 2013 at 7:44

SteveC

17k25 gold badges116 silver badges180 bronze badges

answered Apr 3, 2013 at 9:44

Our Man in Bananas

6,00525 gold badges104 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lionel T. Over a year ago

sometimes, while running non-english windows you might get error 2507, change "ODBC Database" for "ODBC" (works with French). (Thanks to tony gil: stackoverflow.com/questions/5858366/…)

leeand00 Over a year ago

This doesn't work real good if you're trying to automate it, try this instead: stackoverflow.com/a/38266765/18149

Collectives™ on Stack Overflow

How to link Access table to SQL Server with VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related