How to link tables to ODBC with VBA? (1 Viewer)

munhun

New member
Local time
Today, 02:31
Joined
Nov 23, 2018
Messages
8
Hello.

I use MySQL as a database.
I connected the database using ODBC Administrator.

However, I found this to be a security problem.
When someone connects to this ODBC, all database information can be viewed and modified.

Therefore, I found that I need to connect to VBA automatically without using ODBC Administrator.

I found the following code:

This shows the following error in ".TableDefs.Append tdf".
error message : Could not find installable ISAM file.

Private Function LinkTable()
Dim tdf As New DAO.TableDef

With CurrentDb

.TableDefs.Refresh
Set tdf = .CreateTableDef("tablename")
tdf.Connect = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=xxx.xxx.xxx.xxx;Port=3306;Database=database;User=username;Password=pass;"
tdf.SourceTableName = "tablename"
.TableDefs.Append tdf
.TableDefs.Refresh

End With
Set tdf = Nothing
End Function

There is also an expected problem.
When opening the first access file, you should run the connection logic first.
Please show the complete code including this problem.

Thank you.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:31
Joined
Jan 20, 2009
Messages
12,849
Sounds like you don't have the driver installed on the machine.
 

munhun

New member
Local time
Today, 02:31
Joined
Nov 23, 2018
Messages
8
Sounds like you don't have the driver installed on the machine.

I used MS-ACCESS.
I am currently using it as an ODBC manager.

I gave the program permission through user login.
The problem is that when someone connects to the ODBC manager, they see all the DB information.

Therefore, I need to connect the tables in VBA without going through ODBC Manager.
 

bastanu

AWF VIP
Local time
Today, 02:31
Joined
Apr 13, 2010
Messages
1,401
As Galaxiom said, it looks like you might not have the MySQL driver installed (MySQL ODBC 8.0 Unicode Driver). Can you link the table using the built-in wizard then inspect the connection string in the system table (look in the msysObjects in the Connect field).
Instead of using the tabledefs collection I use the Docmd.TransferDatabase method of the application object:
DoCmd.TransferDatabase acLink, "ODBC Database", strConnect, acTable, IIf(sSourceTable = "", sLocalTable, sSourceTable), sLocalTable, False, True.
(I store the connection string strConnect in a user system table but you can hard-code it in VBA if you wish).

Cheers,
Vlad
 

munhun

New member
Local time
Today, 02:31
Joined
Nov 23, 2018
Messages
8
As Galaxiom said, it looks like you might not have the MySQL driver installed (MySQL ODBC 8.0 Unicode Driver). Can you link the table using the built-in wizard then inspect the connection string in the system table (look in the msysObjects in the Connect field).
Instead of using the tabledefs collection I use the Docmd.TransferDatabase method of the application object:
DoCmd.TransferDatabase acLink, "ODBC Database", strConnect, acTable, IIf(sSourceTable = "", sLocalTable, sSourceTable), sLocalTable, False, True.
(I store the connection string strConnect in a user system table but you can hard-code it in VBA if you wish).

Cheers,
Vlad

"MySQL ODBC 8.0 Unicode driver" is installed and the program is normal.

DoCmd.TransferDatabase acLink, "ODBC Database", "Driver={MySQL ODBC 8.0 UNICODE Driver};Server=xxx.xxx.xxx.xxx;port=3306;Database=dbname;User=username;Password=password;", acTable, "testTable", "testTable", False, True

error message : Could not find installable ISAM file.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:31
Joined
Feb 19, 2013
Messages
16,553
However, I found this to be a security problem.
When someone connects to this ODBC, all database information can be viewed and modified
it will still be a security issue - the newly created linked table will still be visible to the user if they have use of the navigation pane. As a small deterrent, make the table hidden using

Code:
application.sethiddenattribute actable, "tblName", true
although user can still unhide it in file>options

At more of a 'hacker' level, if the user has the file open and has run the code to create a linked table, they can open another db and copy the table objects across or use vba to inspect the tabledefs.collection.

Or a user can simply look in the database documenter or msysobjects table to find the connection string, then create their own linked table

There are ways and means to make this more secure, You should create a security policy - what you want to protect, from whom, from doing what.

From your solution, your policy is to protect data from being modified by unsophisticated/inexperienced users by directly working on tables. This may be sufficient for your needs, but do consider the wider requirements.
 

bastanu

AWF VIP
Local time
Today, 02:31
Joined
Apr 13, 2010
Messages
1,401
Is the connection string you show the exact one from the Connect field in the msysyobjects after you linked the table using the wizard?
 

bastanu

AWF VIP
Local time
Today, 02:31
Joined
Apr 13, 2010
Messages
1,401
In the connection string try Pwd= instead of Password=

Vlad
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:31
Joined
Feb 19, 2013
Messages
16,553
"MySQL ODBC 8.0 Unicode driver" is installed and the program is normal.
not sure what 'program is normal' means,

the driver may be installed but "MySQL ODBC 8.0 Unicode driver" may not be the driver required for your version of MySQL.

Please confirm you have successfully linked to MySQL using the New Data Source>Other>ODBC.

Also confirm what is stored in MsysObjects for this table in the connect and database fields - you can run this query

Code:
SELECT [Connect], [Database] FROM mSysObjects WHERE [Database] is not null OR [Connect] is not null

This should tell you what your connecting string should look like
 

Users who are viewing this thread

Top Bottom