I have looked at several similar questions so this is not for lack of trying. I want to create a DSN to Postgres using VBA. I specifically don't want to use a DSN-less connection in Access. I'm suspecting it could be my connection string rather than the code but I'm not sure and I don't get any errors its just unsuccessful.
My code is as follows:
Option Compare Database
Option Explicit
Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
(ByVal hwndParent As Long, ByVal fRequest As Long, _
ByVal lpszDriver As String, ByVal lpszAttributes As String) _
As Long
Private Const ODBC_ADD_SYS_DSN = 4
Public Function CreateDSN(Driver As String, Attributes As _
String) As Boolean
CreateDSN = SQLConfigDataSource(0&, ODBC_ADD_SYS_DSN, _
Driver, Attributes)
End Function
Sub test()
Dim strConnection As String
strConnection = "ODBC;DSN=Postgres_Test;Driver=PostgreSQL Unicode;Server=************.*********.***.****;Port=*****;Database=example;Uid=********;Pwd=****************;"
Debug.Print CreateDSN("PostgreSQL Unicode", strConnection)
End Sub
All I have to go on here is False in the immediate window. If someone could confirm if it is just the connection string (and what the right syntax is) that'd be useful. I tried looking at the properties of a linked table in Access of one where I manually created the DSN and its that and this that I used to generate the one I'm using already.
SQLConfigDataSource doesn't take a connection string. It takes a driver name, and attributes.
Let's dissect the connection string:
ODBC; : DAO-specific prefix indicating an ODBC connection string. Never needed outside of Access/DAO.
DSN=Postgres_Test: DSN name
Driver=PostgreSQL Unicode: Driver name, should never be combined with DSN name in a connection string as the DSN specifies the driver name
Server=************.*********.***.****;Port=*****;Database=example;Uid=********;Pwd=****************;: Driver-specific attributes.
If we look at the documentation, the driver attributes should be null-separated, not separated by ;, and the string should end with a double null separator.
So, the final call would need to look like this:
CreateDSN = SQLConfigDataSource(0&, ODBC_ADD_SYS_DSN, _
"PostgreSQL Unicode", "DSN=Postgres_Test" & vbNullChar & "SERVER=***" & vbNullChar & "Port=*****" & vbNullChar & 'Etc)
Making sure that to end on a vbNullChar
Furthermore, fRequest is a Word, and a Word corresponds to an Integer in VBA, so your declaration should be adjusted for that.
However, as Max pointed out, Access has a built-in for registering DSNs, and you should probably just use that, as it's way easier.