use code to link external mdb table

How to use code to link to a table in another Access mdb file?

Post your answer or comment

comments powered by Disqus
I splited program and database as two mdb file, app.mdb and data.mdb. All tables are stored in the data.mdb file.
How to use code to link all tables in the data.mdb whe app.mdb open every time?

I don't see this one already posted, so heeeere we go.

I want to be able to link external tables programmatically by using a macro to do the linking. The macro would be fed table names and I want it to make the links, looping back to repeat until it runs out of values/table names. My specific question is -- how do you call the linking process from within the macro? I know about the Run Command | Link Tables option, but it just presents a dialog box to browse to the table - same as if I were to do it manually. I don't want to see the dialog box - I want to to supply the table name as a variable, then have the macro do the link. Seems simple, but I don't see a way to do this. Can anyone tell me what I am missing?

Thanking you in advance,
Scott/Chicago, IL USA

I think this is a common issue for all those people who are upgrading from access to SQL server (as database), so i'm asking where i can find some resource to accomplish the following "basic" task.

The following command is used to attach an Access Table contained in an .mdb file:
DoCmd.TransferDatabase acLink, "Microsoft Access", "c:myappmydb.mdb", acTable, "target tablename", "local tablename"

How do you attach a table contained in an SQL server 2005 database?
Say that the database is on a server called MYSQLSERV, which contains a database called MYAPPDATABASE, that contains the table "MYTABLE"...

I tried the following, but i didn't succeed:
I created an "ODBC source" called MYODBC, linked to the correct server and to the correct database (I tested and I was able to link and browse the data in a table), then I tried this command:
DoCmd.TransferDatabase acLink, "ODBC Databases", "MYODBC", acTable, "target tablename", "local tablename"
I got the following error:
Runtime error 2507: The ODBC Databases type isn't an instyalled database type or doesn't support the operation you choose.
What am I missing?

Hello Everyone,

I know it can be done and I have seen some code snippets before, but how can I use Access vba code to count all forms, tables, queries, reports, vba modules, objects to get a total count of each for an Access database?

Any help is greatly appreciated.



There are about 500 tables in SQL server.
How to code to link all SQL server tables via odbc?

Is it possible to link external file again when database is ready and the source file has been moved to other location?
And going farther -> is it possible to create some kind of dialog wich prompts user to point the external file as linked?


I searched and couldn't find anything on this specific issue.

I'm on the verge of designing a pretty big database and I've run into what may become a bit of a problem. There will probably be several backends in different locations. Should another backend ever be created or if the location for one changes, the user can use a form to refresh the links to the tables on that specific database.

The way I've done this so far is by having a piece of code that checks the tables for a connection string. If the table has a connection string, its a linked table, so the link gets deleted and replaced with the new link. Well this will no long work since there will be several backends and only 1 of them may need its tables relinked and using that code will delete all the table links, including the good ones.

Is there a way to make 'DoCmd.TransferDatabase' overwrite table names instead of giving the duplicate tables a number suffix? I think that doing this would be easier than retrieving the table names from the new backend and comparing them with the linked table names in the frontend so the old links could then be deleted.

I have used an Access 2003 MDB to link to two .db files in a Paradox Database while the PC is running with Windows XP.
The Paradox Database is located within a sub-folder in "Program Files". There has been no problem, so far.

I have now transferred both the Access MDB and the Paradox database to a new PC running with Vista Home Premium.
The Paradox Database is located within C:UsersPublicPublic Documents (by the Program which normally reads/writes to them, and still works) and the Access MDB is located in a sub-folder on a D: Drive.

I can open the MDB and attempt to re-link to the .db files, but an error is flagged up by the Linked Table Manager "Unexpected error from external database driver (11265)" with an OK button to close.

I have tried copying the Database and the Access MDB together to a folder on the D: drive, but the same error message appears.

I am unable to find any mention of this message in Microsoft Knowledgebase - can anybody advise, please?


Basically I am trying to link a SQl Table through code without having to go to tools and then using add linked table manager and then refresh.The above steps I am trying to code.
I have this function in a module and use the command button of a form to call the function. Though I hard coded the DSN to the database belonging the SQL Sever, still prompts me for a user to select the DSN, how could I avoid it. I am breaking my heads. Please any advice.
The DSN already exists and is created through Settings then control panel and then Data Sources(ODBC).
I have no clues as to what is going on.

Thanks in advance

This is my code

Public Function Linktabledao()

Dim strlinkname As String
Dim strdbname As String
Dim strtablename As String
Dim strdsnname As String
'links or relinks a single table
'returns true or false based on err value

strlinkname = "satya"
strdsnname = "Freslib Production"
strtablename = "satya"
strdbname = "Freslib"

Dim db As Database
Dim tdf As TableDef

On Error Resume Next
Set db = CurrentDb
'if the link already exists, delete it
Set tdf = db.TableDefs(strlinkname)
If Err.Number = 0 Then
db.TableDefs.Delete strlinkname
'ignore error and reset
Err.Number = 0
End If
'create a new tablede object
Set tdf = db.CreateTableDef("satya")
tdf.Connect = "ODBC;Database = " & strdbname & ";DSN = " & strdsnname & ";Integrated Securtiy = True"
tdf.SourceTableName = strtablename

'append to the database's tabledefs collection

db.TableDefs.Append tdf
Linktabledao = (Err = 0)

End Function

Hi all

I am trying to write the code to delete an existing table, create a new one linked to a SQL Server db using OLE DB Provider connection string and Trused Connection

unfortunately I get an error 'could not find installable ISAM' when I try to append my new table top the tabledefs collectionin my Access DB (MS Access 2003).

Quote: Dim sConnStr As String
Dim oTable As TableDef
Dim sDestinationTable As String
Dim dbs As Database
Dim tbl As DAO.TableDef
Dim tblLinked As DAO.TableDef

sDestinationTable = "SQL Rulesnew"
Set dbs = CurrentDb
CurrentDb.CreateTableDef "SQLRules_" & VBA.Format(Now, "ddmmyyyy")

sConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Hermes_Rep;Data Source=fos"

' below command also wouldn't work so commented!
'DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=Fos;Database=Hermes_Rep;Trusted_Con nection=Yes", acTable, "[Report SQLRulesnew]", "SQLRules" & VBA.Format(Now, "ddmmyyyy")

' if table exists, delete it
If DCount("*", "MSysObjects", "[Name]='[SQL Rulesnew]' AND [Type] In (1, 4, 6)") > 0 Then
DoCmd.DeleteObject acTable, sDestinationTable
End If

Set tblLinked = dbs.CreateTableDef(sDestinationTable)

tblLinked.Connect = sConnStr
tblLinked.SourceTableName = sDestinationTable
dbs.TableDefs.Append tblLinked
tblLinked.RefreshLink notice that the source table name is [Report SQLRulesnew] so I believe I have to enclose it in square brackets is that right?

Also, is it possible to just iomport the data without a link?

I tried using Docmd.TransferTable with acImport but no luck with that either.

thanks for showing me where I am going wrong.

I am an extremely frustrated (And/Or slow) learner who has been trying to
learn how to use code in "M.S.Access" (On and off for about a year now)
but without much success. I have embarked on what should be a VERY simple
"Timesheet" project.

Its easy to add and edit records using the Switchboard Manager tools ETC,
but I have no idea how to validate (and then ADD or NOT add) data BEFORE
the database is updated.

My Database consists of 2 tables - called "Users" and
"timesheet" respectively.

The "Users" table consists of the following fields.
UserID Text - Primary Key
Name Text
Surname Text
LoggedIn Yes/No
LastLoggedIn Date (Medium)

The "TimeSheet" table contains the fields
Record AutoNumber (Primary Key)
UserId Text
Date Date (Medium)
TimeIn Time (Medium)
TimeOut Time (Medium)
Remarks Text

The very first form is used to "AddNewUsers" and should perform
the following checks.

1. A user cannot enter an exisiting "UserID" by virtue of the fact
that the "UserID" is the Primary key. (No duplicates allowed)

2. A "UserID" must consist of at least 3 characters and 1 number.
(Surprize - I know how to check this.)

3. Check that the user is not adding a duplicate record with
a different code. (I.E. Check that "Name" and "Surname"
does not exists already.)

A. How do I convert the text to uppercase before writting it
to the table? (This to ensure that the data is always
entered in the same case for validation purposes.)
I keep getting an error when using the "Upper" , "ConverToUpper"
orthe "UpperCase" function.
(Is "Function" the correctword to use here ??)

B. I tried to create the "AddNewUsers" form where the fields are
NOT bound to any records, but when trying to check if a user
already exists, I got an error saying that a field must be
bound to a record. I therefore included the "UserID" field
on the form.

Surely If I am adding a new record, then the fields should not
be bound ? There must be a way around this problem ?

c. The "AddNewUsers" form contains the following fields....
MyUserID - To get the "UserID"
MyName - Get the users name
MySurname - Get the users Surname
UserID - Listbox (So that the form is bound to
records in some way.)

....and (Taking tips from the so-called "Help") the following code.
(Please see comments in this code.)

Private Sub MySurname_LostFocus()
Dim dbsTimesheet As Database
Dim rstMyUsers As Recordset
Dim strUserName As String
Dim strUserName2 As String

' set the database and recordset variables
Set dbsTimesheet = OpenDatabase("timesheet.mdb")
Set rstMyUsers = dbsTimesheet.OpenRecordset( _
"SELECT UserId, Name, Surname from Users", dbOpenSnapshot)

'How do I get the system NOT to re-open the database? - I tried
'the following statement but then I get an error at the point
'at the "With rstMyUsers..... End With".......
'rstMyUsers = "SELECT UserId, Name, Surname from Users"

'Set the strUserNamevariables
strUserName = "Name = " & "'" & MyName & "'" & " and Surname = " & "'" & MySurname & "'"
strUserName2 = " " & MyName & " " & MySurname

With rstMyUsers
' Populate recordset.
' Find first record satisfying search string. Exit
' loop if no such record exists.
.FindFirst strUserName

'If the user details are not found, then
'add a new record to the database.
If .NoMatch Then
UserId = Text10
Name = MyName
Surname = MySurname
'If user already exists tell the use and .....
MsgBox "A record already exists for " & strUserName2
'...Reset the field names to blank
Text10 = ""
MyName = ""
MySurname = ""
End If
End With

End Sub

P.S. I am in possession of the following books, but have not been able to
find any help for my specific questions.

1. Mastering Access.
2. SAMS - Teach yourself Visual Basic in 24 Hours.
3. Examprep - Visual Basic desktop.

Is there a manual / tutorial that teaches how to create a "PROPER" application ?
I have found that any of the "northwind" type samples do not describe howe
to do validation as described above. I.E. Customers, Suppliers and even
Staff can be dulicated in these sample databases.

ANY help would be truly appreciated.

I am creating a system that logs visits to site, using a sql backend, access front end. Now loading the front end I have a procedure which creates a local mdb file in the users my documents folder. The purpose of the local mdb file is that it gives the user an offline record of all their visits.

When there are design changes which involve additional tables adding to the local mdb, I want to run on startup a sql query to check whether table exists in the local mdb. So I do a Select Query of MSysObjects where name='tblname'.

However when I load up the front end I get an error saying Record cannot be read, no read permission of MsysObject. Now I can obviously go into the VB Created Mdb and alter the permissions. However that is no good when I need to distribute to 100 workstations.

What I need is, VB code to alter the permissions on the MsysObject, when I create the Local Mdb.

Any Idea's??

Hi everyone,

I'm using 'SELECT Null As Column INTO TempTable FROM ExistingTable' type statements to populate a temporary table, rather than an ALTER TABLE statement.

When using 'Null As' the data type in the table is binary.

Using 'No As' results in a number type data.

What I am interested in is if anyone knows what the code would be to create String and Date data types, or if anyone knows any useful links/resources for this.

I've looked all over for anything to do with this, any help would be greatly appreciated.


The Poot

I am charged with accessing all Excel applications on a server programmatically checking for code and hard links to SQL Server and Oracle with hard coded passwords, i.e. looking for connection strings to external databases with hard coded passwords.

I've done this before using Access to check table links but not the VBA code by attaching external mdb tables to my database and then checking the MSysObject table connections for each table. I didn't check the VBA code for coded connections.

I really need to check the Access and Excel VBA code too with from an external program.

Any suggestions or direction would be appreciated.

Thank you in advance for your courtesies.

I would like to delete tables using code where part of the table names have a certain value.

So the table have as their first 13 characters the following string


I would like code to iterate through the tables collection and delete all tables that have this as the first 13 characters of their name..not sure of the exact syntax (e.g. is it tabledef or tabledefs), but something like:

dim tbl as tabledef(s)

for each tbl in tabledef(s)
where left(tblname,13) = 'tbl_req_tmp_a"
docmd.deleteobject actTable, tbl
next tbl

Thanks for any assistance..

I have a table (tblCovers), and in that table one of the columns (OpenCover) is a 'Yes/No' column/field.

Using code, how can I set all fields in that column to 'No', when clikcing a command button?

Best regards

I'm trying to use code to relink the the table but that is not the only table that is linked in the DB. The other linked tables are in other databases so I want to select specific tables to relink as the others may not need it. I alway will want to select the path.

here is an example of the code I'm using I got it from one of the other users here.

I'm trying to figure out how to link a text file to my database with VBA. I know how to do this for other types of data sources, using the DoCmd.TransferDatabase acLink command, but need guidance using this with TXT files.



I found 2 examples of code to refresh my attached table link to sharepoint lists.

I have a scheduled task open my Access 2010 db and an autoexec macro runs and closes the db.

My problem is my tables disconnect from sharepoint 2010 and the update fails.

I added the code I found to a module and added a line in my macro to run the code. Now I cannot get the code to work.

Am I at least on the right track? I just want to know before I spend many hours getting this code to work.

These are the two locations of code I found.

I think part of my problem is that I have a regular table that I add a txt file to that is not linked to sharepoint so I need to exclude it from the code.

Thanks for any advice


I am hoping someone is able to help me, I have setup a new database but need some assistance in setting up some SQL code which I havent done before.

I have two tables, one is linked to Sharepoint (Completions) and one is linked to an excel file (Import). I need to run a query that goes through the Import table and removes any duplicate lines where the [Matter Number] and [Completion Date] are the same so that only one line is remaining. Once this has been done I need to compare the import table against the Completions table and again remove any entries from the import file where the [Matter Number] and [Completion Date] already appear in the Completions table.

Then anything that is left in the Import table needs to be moved across to the Completions table.

Thanks in advance for your assistance.


Hi everyone,
I'm new to the forum because I have a unique question that doesn't seem to be answered anywhere in the far reaches of Google (at least that I've seen).

I'm trying to export multiple Access tables to tab delimited .txt files. Other people may have different versions of the database and it has to upgrade for them too, so I can't specify tables. I have to do it generically with a loop.

acExportDelim defaults to commas, which won't do because of the data within the DB. I have successfully used export specs to make specific tables tab delimited, but the specs are table-specific, and there doesn't seem to be a way to globally set them. Even if there were, I'm trying to write a script that anyone can run regardless of the table names they have. I was wondering if there was a way that I could use my code to specify a spec to each table without having to manually go into the export menu for each table. My current code is below.

Sub Main()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim tdfs As DAO.TableDefs

Set dbs = CurrentDb()

For Each tdf In tdfs

' Check to see if ! system table
If Left(tdf.Name, 4) "MSys" Then
'Output tables to (table name).txt using "newSpec" specification, with no table headings
DoCmd.TransferText acExportDelim, "newSpec", tdf.Name, "C:Documents and SettingsssylvesDesktopdat" + tdf.Name + ".txt", False
End If


End Sub

I need to connect to an external .MDB (version 2.0) and run queries against it from within a form. The database requires that I enter a password. Which connection type should I use?

Ametuer Database player here, having a real blonde day, just spent 20 minutes trying to figure out how to post a message.

I have a form that is based on multiple tables in a query for the purpose of Mr "I Hate computers" to view product information. Have a second form on single table with subforms etc for Data entry Purpose.

Have had a command button (now lost) on "view only" form to go to the matching record in the "Edit Form". Can't get it to link to matching record anymore. The PRODUCTID Field is a TEXT field in the main table.

Click Event Procedure is as follows

Private Sub CmdOpenProd_Click()
On Error GoTo Err_CmdOpenProd_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FrmAllProducts"

stLinkCriteria = "[ProductID]=" & "'" & Me![ProductID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit Sub
MsgBox Err.Description
Resume Exit_CmdOpenProd_Click

End Sub

I'm not sure now whether I had the original connection on a Macro or Command Button, but I can't seem to make either work.

Any Help Greatly appeciated. Now Backup up my forms!!!


How to use Code (Example : send keys) to unprotect VBA project in Access 2003 and 2007.

Not finding an answer? Try a Google search.