add table using vba

[ahmed_optom]

Im trying to link a table to a access file.

I can do it manually fine, but I am trying to do it with vba. I have used tabledef and tried to connect using odbc and oldeb but i get isam errors.

sConnect = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=Z:\Documents\PMS\Database_be.accdb;Jet OLEDBatabase Password=;"

i get error 3017 no installable isam.

I dont think its because i need to install something, as it works if I do it manually.

any ideas?

 

[theDBguy]

Hi,

Have you tried using the TransferDatabase method?

 

[isladogs]

I rarely use ADO - much prefer DAO

The connection string for linked Access tables should be something like this:

"MS Access;PWD=" & STR_PASSWORD & ";DATABASE=" & databasepath&name.accdb

See if this code helps you link an Access table (different code is needed for a SQL Server table)

Function LinkTable(pstrTableName As String, pstrTableAlias As String, pstrODBC As String, Optional pbooSilent As Boolean) As Boolean
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim td As DAO.TableDef
    Dim booSilent As Boolean
 
    If IsMissing(pbooSilent) Then
       booSilent = False
    Else
       booSilent = pbooSilent
    End If
    
    If pstrTableAlias = "" Then pstrTableAlias = pstrTableName
    
    Set db = CurrentDb()
    
    Set td = db.CreateTableDef(pstrTableAlias)
    If InStr(pstrODBC, ";PWD=") > 0 Then
       td.Attributes = dbAttachSavePWD
    End If
    td.Connect = pstrODBC
    td.SourceTableName = pstrTableName
    
    db.TableDefs.Append td
    
    If Not pbooSilent Then
       MsgBox pstrTableName & " has been linked."
    End If
    
    LinkTable = True
End Function

'Example usage . . .

LinkTable("tblStudents", "tblStudents", "MS Access;PWD=xxxx;DATABASE=C:\Users\cridd\Desktop\SDALink\DummyMaster\SDA4_BE.accdb", True)
Application.RefreshDatabaseWindow