There are several tables with that name

[jaryszek]

Hi i am trying to refresh link but method below is failed:

Function RemoveAndConnectToOdbcTables()


Dim username, pass, strConnect, strConnectQuery As String
Dim i As Long


username = Environ("username")
pass = "PasswordAccess"


'''You can use a pass-through query to list the table names from your SQL Server database. Open a recordset based on that query. Then loop through the recordset rows and link each table.
On Error GoTo ErroHandler


username = "admin"
pass = "PasswordAccess"


strConnect = "ODBC;DRIVER={PostgreSQL Unicode};" & _
"DATABASE=AccessLog;" & _
"SERVER=localhost;" & _
"PORT=5432;" & _
"UID=" & username & ";" & _
"PWD=" & pass & ";" & _
"Trusted_Connection=Yes;"


strConnectQuery = "ODBC;DRIVER={PostgreSQL Unicode};" & _
"DATABASE=AccessLog;" & _
"SERVER=localhost;" & _
"PORT=5432;" & _
"UID=" & username & ";" & _
"PWD=" & pass & ";" & _
"Trusted_Connection=Yes;"


 DoCmd.Hourglass True
    Dim db As DAO.Database
    Dim tdef As TableDef
    Dim qdef As QueryDef
    Dim missingTbl() As String
    Dim upper As Integer
    Dim strMsg As String
    Dim tdfCurrent As TableDef
    Dim TableName As String
    
    Set db = CurrentDb
    For i = 0 To CurrentDb.TableDefs.Count - 1
        Set tdef = db.TableDefs(i)
        Debug.Print tdef.Name
        TableName = tdef.Name
  ''          If Len(tdef.Connect) > 0 And Left(tdef.Name, 1) = "t" Then
            If Not (tdef.Name Like "MSys*" Or tdef.Name Like "~*") Then
                DoCmd.DeleteObject acTable, tdef.Name
                Set tdfCurrent = db.CreateTableDef(tdef.Name)
                tdfCurrent.Connect = strConnect
                tdfCurrent.SourceTableName = tdef.Name
                db.TableDefs.Append tdfCurrent


                db.TableDefs(TableName).RefreshLink
            End If
    Next i


    For Each qdef In CurrentDb.QueryDefs
    Debug.Print qdef.Name
        If qdef.Type = dbQSQLPassThrough Then
            qdef.Connect = strConnect
        End If
    Next qdef
    
    DoCmd.Hourglass False
    
Exit Function


ErroHandler:
MsgBox "Error number is: " & Err.Number & "; " & Err.Description
DoCmd.Hourglass False


End Function

error is:

https://www.google.pl/search?q=there...hrome&ie=UTF-8

but i do not understand this,
can anyone help?

Best,
Jacek

[jaryszek]

Anyone?

Jacek

[jaryszek]

Nobody is removing linked table and access them using DNS-less connection?

Best,
Jacek

[Minty]

Hi Jacek,

I do but with SQL server, so I'm not sure the methods I use are the same.
The definitive guide is here http://www.accessmvp.com/djsteele/DSNLessLinks.html