ODBC failed while connecting to postgresql database (1 Viewer)

jaryszek

Registered User.
Local time
Today, 07:27
Joined
Aug 25, 2016
Messages
756
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.

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



1616406769814.png
 

Attachments

  • Screenshot_139.png
    Screenshot_139.png
    12.4 KB · Views: 508
Last edited:

bastanu

AWF VIP
Local time
Today, 07:27
Joined
Apr 13, 2010
Messages
1,401
Haven't done much work with PostgresSQL but can you try to create a view with a name compatible with the ODBC driver (i.e. T_1_0_0) and import that instead?

Cheers,
 

jaryszek

Registered User.
Local time
Today, 07:27
Joined
Aug 25, 2016
Messages
756
Hi! Thank you very much i will check your solution

Jacek
 

Users who are viewing this thread

Top Bottom