Hi Vlad,
i was trying to use code:
Code:
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
but it failed. Additionaly i tried to use tdfCurrent.refreshlink but nothing happen...
I do not now why?!
Best,
Jacek