Creating ODBC DSN using VBA

Question

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.

I do not see where you actually register the the database. this works fine for me: Dim strAttributes As String strAttributes = "Database=mydatabase_Zentral" & _ vbCr & "Description=mydatab_ZENTRAL" & _ vbCr & "OemToAnsi=No" & _ vbCr & "Server=serverXYZ01" & _ vbCr & "Trusted_Connection=Yes" DBEngine.RegisterDatabase "my_database", "SQL Server", True, strAttributes — Max
– Max, Commented Feb 10, 2020 at 10:34

Erik A · Accepted Answer · 2020-02-10 12:13:46Z

2

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.

answered Feb 10, 2020 at 12:13

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Edwards Over a year ago

Thanks, I have tried Max's RegisterDatabase part but so far unsuccessfully but again its probably the difference in arguments between the SQL Server example and what I need for Postgres. I'll look at both ways and see which I can get working the best.

Tim Edwards Over a year ago

I managed to get it working with the RegisterDatabase code but your help with the null-separating was also key.

Collectives™ on Stack Overflow

Creating ODBC DSN using VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related