MSaccess How can I export linked tables ODBC connection build for source control, (including table relationships)

Question

I want to move my Microsoft Access Database front end into source control for easy saving and deployment as well as have some control over changes. I am using code from this question to export queries, forms, macros, reports, however not tables.

There must be a way through vbscripts that I could export the link table build process as well as put it back in.

Does anybody know of anything that could easily let me export my linked tables.

(It would be easier for me to change my linked tables to link to a development server if I could build out the table linking to a text files and manipulate the connection strings from there.)

How do you use version control with Access development?

Or the question could also be, how do I painlessly change my link table connection source without messing with my computer's odbc dns connections?

score 2 · Accepted Answer · edited May 23 '17 at 12:03

2

You didn't specify if you are using .mdb or .accdb files.
I never used .accdb, so I can only tell you about .mdb:

Every .mdb file has a hidden table named MSysObjects.
It contains all objects including forms and reports and stuff, but you can filter on the Type column to get only the tables:

Type = 1 --> local tables
Type = 4 --> ODBC linked tabled
Type = 6 --> linked tables from other Access databases

There are four columns in MSysObjects which contain everything you need:

Connect --> ODBC connection string (if connected via ODBC)
Database --> path to source Access database (if linked to another Access database)
(you probably don't need this because you say you're using ODBC - I included it anyway for the sake of completeness)
Foreign name --> original name of the table in the source database
Name --> name of the linked table in this database

That's all the information you need to re-link the tables.
You don't need to mess around with the computer's ODBC DSNs for this...in fact, you don't need DSNs on the computer at all.

Here is a quick example how to do this:
VBA Code to Add Linked Table with Primary Key

edited May 23 '17 at 12:03

Community

1
1

answered Apr 23 '12 at 22:07

Christian Specht

35,843
15
128
182

THANK YOU SO MUCH... This is much easier! – Mallow Apr 23 '12 at 22:13
My table are type 4, where can I see a complete list of all the types and their association? – Mallow Apr 24 '12 at 20:23
MSysObjects is unsupported, so you have to look around for information. Type 4 is for ODBC linked tables. Some notes : http://wiki.lessthandot.com/index.php/Table,_Query,_Form,_Report,_Module_Exists – Fionnuala Apr 24 '12 at 20:36
My mistake, I mixed up the table types. I just corrected the information in my answer! – Christian Specht Apr 24 '12 at 21:04

MSaccess How can I export linked tables ODBC connection build for source control, (including table relationships)

1 Answers1