hi Guys,
i want to import tables from postgresql into Access using Link tables.
How can i do this if table name is something like (in postgres, i can not change it is part of a model):
vn-bundle-majax:analytics:1.0.0.TableName
This is why that in postgres schema name is "vn-bundle-majax:analytics:1.0.0" which can not be read by Access - there are invalid characters.
The name has just invalid characters for Access to implement as table name.
When i added TestTable it is importing correctly using code below:
connection string like in attachment.
So it is working when table name is allowed by Access, if not i am getting error.
How to connect to this strange tablename?
Best,
Jacek
i want to import tables from postgresql into Access using Link tables.
How can i do this if table name is something like (in postgres, i can not change it is part of a model):
vn-bundle-majax:analytics:1.0.0.TableName
This is why that in postgres schema name is "vn-bundle-majax:analytics:1.0.0" which can not be read by Access - there are invalid characters.
Code:
Sub GetCustomers()
Dim oConn As New ADODB.Connection
Dim cmd As New ADODB.Command
' Connection Parameters
Dim strUsername As String
Dim strPassword As String
Dim rs As ADODB.Recordset
Dim rsTab As DAO.Recordset
Dim tdf As TableDef
' User:
username = "lukemaster"
pass = "test"
strConnect = ConnSringFunction(username, pass)
oConn.Open strConnect
Set rs = oConn.OpenSchema(adSchemaTables)
Set db = CurrentDb()
Set rsTab = db.OpenRecordset("AllTablePostgres")
strConnect = "ODBC;" & strConnect
Dim strSQL As String
strSQL = "Delete * From AllTablePostgres"
db.Execute strSQL
Do While Not rs.EOF
Debug.Print rs.Fields("TABLE_SCHEMA").Value
TableName = rs.Fields("TABLE_NAME").Value
schemaname = rs.Fields("TABLE_SCHEMA").Value
With rsTab
.AddNew
![TableName] = TableName
![TableSchema] = schemaname
.Update
End With
With db
.TableDefs.Refresh
Set tdf = .CreateTableDef(TableName)
tdf.Connect = strConnect
tdf.SourceTableName = TableName
.TableDefs.Append tdf
.TableDefs.Refresh
End With
rs.MoveNext
Loop
End Sub
Loop
End Sub
The name has just invalid characters for Access to implement as table name.
When i added TestTable it is importing correctly using code below:
connection string like in attachment.
So it is working when table name is allowed by Access, if not i am getting error.
How to connect to this strange tablename?
Best,
Jacek
Attachments
Last edited: