problem with using VBA connection string to postgresql

Maybe some security here?

Best,
Jacek

Hi Guys,

I am using code :

Sub copyingSchemas()

Dim dbCon As Object
Dim rst As Object
Dim username, pass, strConnect As String
username = Environ("username")
pass = "PasswordAccess"
Set dbCon = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
'''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.
username = "postgres"
pass = "1234"
strConnect = "ODBC;DRIVER={PostgreSQL Unicode};" & _
"DATABASE=AccessLog;" & _
"SERVER=localhost;" & _
"PORT=5432;" & _
"UID=" & username & ";" & _
"PWD=" & pass & ";" & _
"Trusted_Connection=Yes;"
dbCon.ConnectionString = strConnect
dbCon.Open
Dim strSQl As String
strSQl = "SELECT * FROM t_cpuinfo();"
rst.Open strSQl
dbCon.Close

End Sub

error is :

Linked Table connection string which is working :

Why?

Please help,
Best,
Jacek

On 06/27/2018 05:27 AM, Łukasz Jarych wrote:

Maybe some security here?

32/64 bit mismatch?

Do you have both the 32 and 64 bit versions of the ODBC driver installed?

Is your application calling the correct version?

Best,
Jacek

Hi Guys,

I am using code :

Sub copyingSchemas()
Dim dbCon As Object
Dim rst As Object
Dim username, pass, strConnect As String
username = Environ("username")
pass = "PasswordAccess"
Set dbCon = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
'''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.
username = "postgres"
pass = "1234"
strConnect = "ODBC;DRIVER={PostgreSQL Unicode};" & _
"DATABASE=AccessLog;" & _
"SERVER=localhost;" & _
"PORT=5432;" & _
"UID=" & username & ";" & _
"PWD=" & pass & ";" & _
"Trusted_Connection=Yes;"
dbCon.ConnectionString = strConnect
dbCon.Open
Dim strSQl As String
strSQl = "SELECT * FROM t_cpuinfo();"
rst.Open strSQl
dbCon.Close

End Sub

error is :

Linked Table connection string which is working :

Why?

Please help,
Best,
Jacek

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

John Pedro answered me,

problem was that i had ODBC in the beginning of connection string...
Strange, but in table connector you have to have ODBC in connection string,
in directly connetion you can not have...

Best

2018-06-27 14:49 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>: