4

Actually I use a ODBC-Connection to connect Ms Acces to tables of a PostgreSQL-DB. I connect them by using the External Data/Import ODBC-Link command. It works fine.

But how can I use VBA to link my tables?

testo
  • 1,052
  • 2
  • 8
  • 24

2 Answers2

5

When using VBA to link a table with ODBC, you can add and APP= argument to specify an application name that will generally show in the properties of the connection on your database server.

For example, here is a sample ODBC Connection string for a linked table:

ODBC;Driver={SQL Server};Server=MyServer\SQLExpress;Database=MyDatabase;APP=My App Title;Trusted_Connection=Yes;

My App Title is the string that will be your Application Name for that connection.

Update 1 In response to further comment by the OP:

Here is sample code to link a table via ODBC in VBA. To facilitate this, you also should always delete the ODBC linked table each time before re-linking it to make sure that your options are respected, and that Microsoft Access updates the schema for the linked table. This example shows a connection string for a SQL Server database, so all you would need to change is the connection string for your PostgreSQL-DB. The remaining VBA code would be the same.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConn As String
Dim ODBCTableName as String
Dim AccessTableName as String

Set db = CurrentDb()
ODBCTableName = "dbo.YourTable"
AccessTableName = "YourTable"
strConn = "ODBC;Driver={SQL Server};Server=YOURSERVER\SQLINSTANCE;Database=MYDATABASE;Trusted_Connection=No;UID=MyUserName;PWD=MyPassword"
db.TableDefs.Refresh
For Each tdf In db.TableDefs
    If tdf.Name = AccessTableName Then
        db.TableDefs.Delete tdf.Name
        Exit For
    End If
Next tdf
Set tdf = db.CreateTableDef(AccessTableName)

'===============================
'If your connection string includes a password
'and you want the password to be saved, include the following 3 lines of code
'to specify the dbAttachSavePWD attribute of the TableDef being created
'If you don't want to save the password, you would omit these 3 lines of code
'===============================
If InStr(strConn, "PWD=") Then
    tdf.Attributes = dbAttachSavePWD
End If

tdf.SourceTableName = ODBCTableName 
tdf.Connect = strConn
db.TableDefs.Append tdf
Jericho Johnson
  • 739
  • 1
  • 5
  • 13
  • I think thats correct. Badly I have to learn to setup the odbc-connection by vba first. – testo Sep 11 '18 at 08:25
  • If you use this code two times (two different tables), doesn't it use two database connections? – testo Sep 11 '18 at 09:56
  • No, it won't cause two database connections to PostgreSQL. Access controls the ODBC connections under the covers. This code is a working sample for one table. You would want to modify this code to loop through your tables/list of tables to be linked. – Jericho Johnson Sep 11 '18 at 10:53
  • Basically the solution works. But settings the application_name is not working, cause I didn't know the correct parameter. APP and or APPLICATION_NAME or similar is all not working. I try to rename the questions title, so your answer will fit. – testo Sep 11 '18 at 11:24
  • @doev Try `ApplicationName=` all one word as the parameter specific to PostgreSQL. – Jericho Johnson Sep 11 '18 at 11:54
  • Or, check out [this post](https://dba.stackexchange.com/a/184837) for an option to change the application name via code. Although, that might only apply to a second connection. – Jericho Johnson Sep 11 '18 at 12:00
  • The VBScript code in that post is identical to what can be used in VBA. – Jericho Johnson Sep 12 '18 at 01:14
  • ok, got it. But finally I need to open a connection to my db over odbc - and not only link tables. As I read I have to use ADO not DAO for this. – testo Sep 13 '18 at 08:19
1

For some reason this code gives Run time error 3170 - Could not find installable ISAM. However, when you add ODBC; at the beginning of the connection string, then it works. So the connection string should look something like:

strConn = "ODBC;DRIVER={MySQL ODBC 5.2 Unicode Driver};" _
& "SERVER=servername;" _
& "DATABASE=databasename;" _
& "UID=username;PWD=password; OPTION=3"
Dejan Ristanovic
  • 49
  • 7