Linking tables in frontend and backend Results

Hi, I just splitted my database into FrontEnd and BackEnd. Now the problem is when I run VBA (from Frontend) which contains append query to create new table from the Backend tables, the new table is created in FrontEnd database. I don't want to create table in Frontend, instead I am trying to create table in Backend and linked to Frontend. Not sure how to create table in Backend and linked to Frontend?
I have already tried to find the solution but I must be using the wrong criteria. Does anyone has example code with bit of explanation?

The description of "Transaction statement (Microsoft Access SQL)" in online help (Access 2007) ends with the following sentence:

Quote: Transactions are not supported for linked tables. I have a nicely working DB, using DAO throughout, and splitting the DB into a frontend and backend would be advantageous for maintenance reasons. But, I have transactions in one particular corner .... and the quoted statement is a bit of a stumbling block, if I understand it correctly.

Is there any other way of having transactions for a split DB?


I have mdb frontend, SQL backend, I am trying to update a
linked tables in linked table manager, i select all the
tables, click prompt for new location and then press ok.
When I used access 2007 I get the error 'ODBC--call failed' and I cannot
even select the dsn I want to change it too. While in 2003 I have been
able to select another dsn with no error.

It works fine with Access 2003 just access 2007.

Any help would be great, shall i try to force a relink by VBA?Cheers,


I have a frontend and backend database put in a network drive where users can access to the frontend version which has tables linked to the backend.

When I set up the link tables from the frontend to the backend, Access points to a local drive letter in my computer. For example, if I map the network drive where the databases are, say to Drive R:, the link table in the frontend will always point to tables in the backend on R:. If other user map the same network drive to drive letter other than R:, the table links between the frontend and the backend will be broken.

I wonder if anyone knows a way to set up table link with a physical path instead of a local drive letter?



Hi everyone, first post here, would be grateful for any forum resources you can point me to that may help. so i have a database broken into frontend and backend. there is a linked table (exists on the backend, is linked on the frontend), called 'master customer list' (should probably be called 'contacts' list as it contains all contacts, not just active customers).

there is also a linked table called 'pickups' which exists in the same way (on the backend, linked in frontend). this keeps track of our service calls to a given customer

'pickups' table looks thru 'customer query' which filters the 'master customer list' table by a status field and only shows active customers. this works in the backend perfectly, but the frontend has a nice form i would rather use so i can look at other customer properties while entering data in a subform.

anyway 'pickups table' on the frontend is broken
'master customer list' and 'pickups' are both linked to the backend.
'customer query' exists on both the backend and frontend (queries cant be linked) and i believe these queries are identical (at least for the fields i need).

so in pickups table I try to look up customer name from customer query using the following command:
SELECT [Customer Query].ID, [Customer Query].[Unique Name] FROM [Customer Query];

this works fine in the backend version of 'pickups' table and returns the actual company name values ina nice drop-down combo box. when i look at the fronend version of 'pickups' table, the IDs are listed instead of the customer name, and the lookup field is always blank upon opening. this is where it gets wierd. if i paste the
SELECT [Customer Query].ID, [Customer Query].[Unique Name] FROM [Customer Query];
here, it shows the customer name, but there are literally 100s of dupes of each customer. if i close table and reopen it, it does not save changes, the ID is shown instead of the customer name, and the lookup table is again blank.

what gives? any suggestion? ie got an older version of the database (with old, obsolete data tho) that works just fine. just thinking theres gotta be a bad relationship somewhere or bad syntax. any help? thanks in advance.

Worth mentioning that SELECT [Customer Query].ID, [Customer Query].[Unique Name] FROM [Customer Query]; in pickups query in the broken frontend returns 917756 rows (when it works, until i close the table, and all changes are lost). Master Customer List contains 2044 rows, customer query contains 449 rows. 2044*449 = 917756, so something is looping and running the query for every table entry. (and i still dont know why the linked Pickups table wont save, maybe the 917756 rows make it bug?)

I am working on an MS Access 2010 database. I need to put the backend of the database on a web server, but apparently it is not possible to connect an MS Access frontend to an MS Access backend on a web server, so I exported the backend tables to a mysql database on the webserver. I was able then to create a dsn and link the tables to the Access frontend.

PROBLEM: The linked tables do not have the same attributes as the tables in the Mysql backend. Required fields are not marked required in the linked tables, and unique indexes are also missing.

I really need to use composite (multi-column) keys for processing, so the missing attributes are a shot in the heart.


Hi all,

My company uses Access for daily operations, which the databases are split into a backend (tables) and frontend by using linked tables. Under multi-user environment, the performance of access over the LAN is very slow at frontend. To solve this, I over some web sites, which advise to create a persistent connection to the linked database, somehow because my VB script is not good, so some questions below would like to ask.

My questions are:
1. How and where to type OpenAllDatabases True / False?
2. Regarding "Sub OpenAllDatabases(pfInit As Boolean)", if I put it in module, how can I call it out in Macro?

Below is the copy of the the web site information.
The DAO OpenDatabase Method

To create a persistent connection to the linked database, open a MS Access database variable in VBA using the DAO OpenDatabase method. Keep this variable open as long as your application is running.

The procedure below supports multiple backend databases. Edit the section with the list of databases to open, then call this when your application starts:

OpenAllDatabases True

When you finish, call this to close the database variables/handles:

OpenAllDatabases False

Here's the procedure code:

Sub OpenAllDatabases(pfInit As Boolean)
' Open a handle to all databases and keep it open during the entire time the application runs.
' Params : pfInit TRUE to initialize (call when application starts)
' FALSE to close (call when application ends)
' From : Total Visual SourceBook

Dim x As Integer
Dim strName As String
Dim strMsg As String

' Maximum number of back end databases to link
Const cintMaxDatabases As Integer = 2

' List of databases kept in a static array so we can close them later
Static dbsOpen() As DAO.Database

If pfInit Then
ReDim dbsOpen(1 To cintMaxDatabases)
For x = 1 To cintMaxDatabases
' Specify your back end databases
Select Case x
Case 1:
strName = "H:folderBackend1.mdb"
Case 2:
strName = "H:folderBackend2.mdb"
End Select
strMsg = ""

On Error Resume Next
Set dbsOpen(x) = OpenDatabase(strName)
If Err.Number > 0 Then
strMsg = "Trouble opening database: " & strName & vbCrLf & _
"Make sure the drive is available." & vbCrLf & _
"Error: " & Err.Description & " (" & Err.Number & ")"
End If

On Error GoTo 0
If strMsg "" Then
MsgBox strMsg
Exit For
End If
Next x
On Error Resume Next
For x = 1 To cintMaxDatabases
Next x
End If
End Sub

Okay - I've posted this question before - but am still having the same problem - I've been working on this for two full days and still haven't resolved it. I am very new so operator error is a possiblity - but I really don't understand what is going on. Here's the situation.

I have an Access2000 database that has been split into a frontend and backend. I then created a mde file from the frontend because there are multiple users and I understand that's the way to go. It has been working just fine for about two months.

The frontend tables are linked to the backend (of course) as well as being linked to two other Access97 databases. There has been no problem with this.

Monday morning (after playing around with some forms on Friday, that I then deleted) I opened up the database and got the following message with Microsoft Visual Basic on the banner:

Error accessing file. Network connection may have been lost.

What is very strange is that the users can still go in, the forms open up, databases are updated. However, when I go into the code behind the forms I get the above error message. Forms that I deleted on Friday are not on the forms tab but are STILL showing up in the project window when I go to look at the code. When I click on one of these phatom forms to delete it from the project window I get an error message "The form name "xxxx" is misspelled or refers to a form that doesn't exits". I am unable to delete them. When I try and change anything on any form in VBA it won't let me and gives the "error accessing file...." message.

I have already repaired and compacted more times than I care to mention. Still a problem. I have created an empty database, imported one query at a time, one form at a time. Works fine until I close and reopen the new database which gives me the same error message. My tables (the only network connections I can think of) all open and display data just fine - so the links still seem to be working. If I click on the form in the form window - they all open. Somehow, it seems like my code and my forms became separated. I've looked at my references - not that I have a clue what to look for - but all "looks" okay to me (nothing saying "missing").

I'm pretty desperate and don't have a clue what else I can do to figure out what happened and how to fix it. Any ideas would be greatly appreciated. Thank you. Marie

I recently used the linked table manager on my database and was quite pleased with the results, until now.

I have to do an excel export from one of the backend linked tables and im not sure how to do it. Is there a way to tell the transferspreadsheet and transferdatabase commands to look outside of the current database?

My only other option is to make temporary tables in the front end on the fly.. which i really wouldnt mind doing, however I cant seem to find a way to allow users to have permissions to do that (using user level security with groups and nothing I seem to do for permissions allows my users to create a table in the frontend.. I can as administrator, but no one else can, even with the exact same permissions as the admin account)...

If anyone can shed light on either of these issues it would be appreciated.


I currently use Access as a frontend with linked tables to a MySQL backend.

I'm need to be able to build a single string containing the field values from several records. In other words I need to be able to gather employee id's and surname's based on variant criteria from an employee table and stitch them together into a single string.

I'm currently achieving this by populating a list box and then using listbox.itemsSelected, Listbox.ItemData etc to build the string. I want though to avoid using a listbox or similar control and instead gather the data in an array or temporary recordset from where I can build the required string.

Can anyone help here? I have no idea what the required syntax is for either populating an array from a table (linked or otherwise) or how to reference that data once in the array. I'm hoping to build a two dimensional array.
From what I've seen in other threads, ADODB or DAO seems to be the way to go although my knowledge in this area is virtually nil .



I want to install 2 files (Frontend mdb and backend mdb) in user environment.

Problem when deploying is, in the frontend mdb, i have to inject (configure) the backend mdb path.
How to do that programaticaly, i mean to use any module/macro or what?

I will put these things in my setup (either a vbscript) or steps to execute in mdb when installing.

Things i need:
1. What is the code to modify "MSysObjects" table?
2. Where should i put the code?

Thanks for providing nice place.


I may be missing something simple here

I am finishing off a access app that I am hoping to sell using the 2010 runtime if the end user does not have access installed.

Now my problem - I have split the DB so if I need to perfom upgrades I do not wipe out existing data. I have done this before but the backend was on a server - this time each user will have a stand alone frontend and backend.

How do I arrange the folder structure on the machine I am developing the app on so when it installed on a user PC that the frontend path is correct to link to the backend not just copying the development machine paths as this has failed so far as it does install in the appdata folder and the backend is in a subfolder but the linked tables are the path of the development machine

I want it to install on windows 7, vista and XP - hope this makes sense



I have a split database on a peer to peer network(6 XP
computers). I have copied the front end to all 6 machines
and linked to the tables in the backend stored on a single
machine. I am having problems with multiple users
simultaneously entering data through forms. It seems to
work fine from any machine as long as it is the only user
with the database open. If two or more have the database
open at the same time, it opens other copies in read only
mode and will not allow new records or edits. I have
checked the form properties and both options for the
dataentry property. When set to yes, on anything but the
host computer, no records are shown in the form and new
records aren't allowed. I have tried importing the
required tables to the front end to make sure its not a
problem with my front end, and it works fine. Problem
arises when trying to access linked tables in the backend.
Help function hasn't given me any clues. Does anyone have
any suggestions. I had an access 97 version of this same
database with the same frontend/backend configuration set
up on a different network (Win 98) and it worked no
problem. Why would I have problems with Access 2002?

I'm having a very strange problem with my Access application. I'm using an Acces frontend application and my data is placed in a MS-SQL 2000 backend database.Because of slow communication between two offices, I have merge replicated my MS-SQL database. I make two version of the frontend, one linked two MS-SQL server 1 and the other to MS-SQL server 2.

When I link the tables in the MS-SQL subscriber database I'm loosing the key in many of my tables - some of the tables don't have the problem. I can't see any different in the tables where it keep the key and in the tables where it doesn't. When I look at the tables from within SQL Server Enterprice Manager the key is there, but when I look from within Acces (where it is linked to) it is not. This is resulting in that I cant add records to the tables with missing keys.

I don't see the problem when linking to tables in the MS-SQL publicher database.

Hope somebody can give me an answer on this.


I'm wondering about the following.

If I would have a backend table with the data kept in tables and a frontend database with forms and reports and queries etc. and linked tables to the backend db, what would be the right way to secure both.

In my situation I have salary information in some of the tables, which should not be wide open to the holy world. I also need to restrict the access of some queries and forms in the frontend database to different users. That means I need to set up a secured frontend database with different usergroups having different object permissions. So in that frontend database the Users Group and the Admin User do not have any permissions any more. Thats why I created a shortcut where I kept the database and workgroup information in the target. Every user needs to open the database via the shortcut.

Unfortunately I was not able to secure both the backend and the frontend database, because the linked tables in the frontend db are only pointing to a database not to a shortcut. Because the backend database is secured as well (no permission to Admin and Users any more) it can't be opened directly - only by shortcut.

Am I right that there is obviously no possibility to have a secured backend and frontend db via the Jet database engine and Access?

I hope somebody can answer my questions. At least I know that I can't do anything else and I did not something wrong

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.

Good morning,

Working on a DB for an organization with Frontend and the tables in backend on their network drive. Of course when I work on it at home the backend is in a different location. I overcome this by taking their copy which is linked correctly removing any objects that I changed and importing new ones from my updated DB. It would be simpler if I could take my updated DB and manually reconfigure the path . I can't seem to figure this out. Any ideas.


I have a newly created access 2007 database and have just started to import old historical data over from excel sheets to my backend. There is one table in particular that has grown very large. It is over 1 million lines and will be well over 2 million by the time it is finished. It is 6 columns wide. The file itself has grown to 61 mb. Any queries, charting, or viewing of the data of any kind has become extremely slow, even if i place the files on my own computer instead of the network. The data is in such a way that I can't see how to create a primary key and the field wich is typically searched through is already indexed in the design view of the table. This indexed field has many duplicates and that is why it isn't a primary key. One site I read suggested keeping the linked backend open as long as the frontend is opened through code, but I am unsure if this would help or how exactly to do it. It was also suggested on the site that I turn off the compact and repair on close, but this seems weird cause I always figured that helped speed.
Any suggestions on what could be done or what to look into would be helpful. Thanks

If you have split your Access application into a FrontEnd and a BackEnd and then decide to move the application to different folder, all your links will fail. This code will reconnect all the links automatically. It also handles passwords correctly.

Note that it is assumed that the FrontEnd and ALL BackEnds are in the SAME folder.

The DEBUG.PRINT lines are there simply to show what is happening. If you wish to use them, remove the comments and run the code from within your VB editor. Also - remember to comment the "db.TableDefs(i).RefreshLink" line so no real changes are made.

Create an Autoexec Macro to call this Function at startup.

Remove the "If Source path Then" line and its associated "End If" to have the reconnection happen every time.

	Function Reconnect()
'*      Use an Autoexec Macro to call this function.         *
'*      It will reconnect all links when the FrontEnd        *
'*      and the BackEnd are in the same directory.           *
'*      It also keeps any password settings intact.          *
'*                                                           *
'*      Uncomment the Debug.Print lines and comment out      *
'*      the "db.TableDefs(i).RefreshLink" line to see        *
'*      structure by running code directly.                  *
'* ***********************************************************
Dim db As DAO.Database, Source As String, pathNew As String, pathOld As String
Dim SourceName As String, i As Integer, j As Integer
Set db = CurrentDb
pathNew = CurrentProject.path & ""
'Debug.Print "New Path = " & pathNew
For i = 0 To db.TableDefs.Count - 1
    If db.TableDefs(i).Connect  "" Then
        Source = db.TableDefs(i).Connect
        SourceName = right(Source, Len(Source) - (InStr(1, Source, "DATABASE=") + 8))
'        Debug.Print "Connect = " & Source
        For j = Len(SourceName) To 1 Step -1
            If Mid(SourceName, j, 1) = Chr(92) Then
                pathOld = Mid(SourceName, 1, j)
                SourceName = Mid(SourceName, j + 1, Len(SourceName))
'                Debug.Print "SourceName = " & SourceName
'                Debug.Print "Old Path = " & pathOld
               Exit For
            End If
        Source = left(Source, Len(Source) - (InStr(1, Source, "DATABASE=") - 10))
'        Debug.Print "Connect Start = " & Source
'        Debug.Print "Old Connect for " & db.TableDefs(i).Name & " = " & db.TableDefs(i).Connect
        If Source  path Then
            db.TableDefs(i).Connect = Source + pathNew + SourceName
        End If
'        Debug.Print "new Connect for " & db.TableDefs(i).Name & " = " & db.TableDefs(i).Connect
    End If
End Function

When I have a frontend with VBA (for table additions etc) and a backend (all tables except Switchboard Items), when i change from my test-backend (USB) to the realtime-backend (Network drive/directory) the tables work, but all VBA calls (events like onLoad etc.) break (i get an error message). The only way I can fix it atm is by using the Compress and repair database, which restores the links between VBA and the forms etc.

What causes this to happen? Is it a known bug in Access 2010?

Not finding an answer? Try a Google search.