Split Database. Update Linked Tables If Back End Moved.
I'm about to move to a back end / front end system, from a single database file. Before I do so, I want to make sure I have all bases covered so to speak.
My back end file will reside on a server. I am aware that path names are sometimes changed by network administrators without warning.
From tests, I notice that, if the path to the back end can't be found, one cannot open the front end database file at all (Access 2003).
My question is: if my back end file gets moved, or the path changed, how can I get into my front end file to update the linked tables.
Many thanks in advance.
I have a split database, trying to figure it out, testing... I used the split wizard. It did what I wanted. all tables linked up and everything. I wanted to see what would happen if I move the BE and then activate the FE. well the FE didnt open any forms or anything. I do have a hidden form that opens when my splash form opens that tries to talk to a table. that might be the reason. but wanted to ask if all the menus are off, the DB window is off and all is off, how do I get in to relink the BE to the FE????? even if I move that hidden form that I was talking about??????
Maybe I can adjust the design a bit, I dont know..
any help is appreciated...
Hello all...I'm a new poster and I'm not very good with Access, but I've been teaching myself for the past 6 months or so and am doing pretty good.
I recently found a tutorial here that detailed how I could keep users out or kick users out that have been idle when I want to make a change to my database. It required me splitting the database, which I did....and everything worked perfectly on 2 of my databases. However, my 3rd database, is giving me problems after the split. Whenever I run a couple queries that basically delete a table and append records to another table using particular criteria...I get a list of records called "moved"....
This only happens after I split my database. Does anyone know of any reason why this may be doing this? Any more info you might need I can try to get you.
I have done the mistake of not keeping a backup, what I did is split my database file access and then created my mde file, I need to change some data on my tables, but I get the msg that I cant edit a linked table, is there a way that I could put things back the way it was, also this path that was created is it possible to change for future purposes (I might have to move the dir into a different path)
Hi, I am not sure where to put this thread hopefully someone here can help me.
I have spent a good deal of time developing a very effective access database for a client. It is split into a front end and back end and connected using the Linked Table Manager when necessary.
The front end is quite loaded with queries, reports, forms, macros etc and the back end simply has the tables.
The front end is about 15 mb and the back about 8 mb.
Current set up is a small workgroup network with about 4 -5 consecutive users at any one given time.
I am looking for some advice on how to set up or facilitate getting the backend on the internet so anyone with a front end can access it outside of the LAN.
Today I set up an account with WebDrive which can connect to and assign a drive letter on your pc to any FTP, HTTP site.
I made the connection with WebDrive and using the linked table manager successfully linked the tables on my local machine to the back end on the FTP server - and actually ran the database at a pretty good rate.
I noticed however that it was downloading the backend to my local pc and once I closed the (a) form it was uploading the data back to the server? Very slow and often threw errors.
This of course is not what I wanted. Has anyone heard of doing this? Any work arounds or setting params would be greatly appreciated.
I have been told to create the DB in ASP but I do not have any idea on how to do that whatsoever...
Any advice or insight would be awesome!
How can I change the tables' splitted file path via vba?
Ex.: c: ables.mdb -> c:ew path ables.mdb
I have a split database (mainly to continue development as opposed to security) and a custom menu that includes the Back Up Database command. Is there a way to back up the front and back ends at the same time. It appears the command is only backing up the front end. I guess I could ask the same question of the Compact and Repair command.
I've developed a database for a client and I want to be able make a backup option on the main switchboard that will backup the back-end database. I'm having a problem because the switchboard is a getting its' commands through a linked table in the back-end so a connection persists and I'm getting an error. Does anyone know the correct syntax to close the connection to the back-end temporarily while the backup is being processed and then restore the connection once it is completed?
I tested the code and it works from a form that isn't linked to the back-end, but I would really like to have it be an option from the main switchboard.
This is what I have so far...
Code:Public Sub BEBackup()
On Error GoTo Err_BEBackup
Dim Connection As String
Dim tbl As TableDef
Dim strFileName As String
Dim strBULocation As String
Dim strError As String
Dim db As Database
Set db = CurrentDb()
strFileName = db.TableDefs("tbl_Employee").Connect
strFileName = Mid(strFileName, InStr(strFileName, "=") + 1)
strBULocation = "c:PDR_Backup" & Format(Now(), "YYYY_MM_DD_HHMMAM/PM") & "_PDR_Backup.bkp"
FileCopy strFileName, strBULocation
MsgBox "File saved as" & vbCrLf & strBULocation, vbInformation, "Backup Complete"
Select Case Err.Number
strError = "The disk is full. Cannot save to this disk"
MsgBox strError, vbCritical, "Disk Full"
strError = "The file is currently open. " & vbCrLf & "The file cannot be backed up at this time."
MsgBox strError, vbCritical, "File Open"
MsgBox "Directory created: 'c:PDR_Backup'", vbInformation, "Directory Created"
FileCopy strFileName, strBULocation
strError = "The database is currently being used by another user." & vbCrLf &_
"You can only backup the database if you are the only person using it." & vbCrLf &_
"Please try again later."
MsgBox strError, vbCritical, "Another User"
MsgBox Err.Description, vbCritical, "Error: " & Err.Number
If I have a number of tables that are linked together with unique ID numbers, how can I set the database up so that when a familiar option is chosen by a user in a form, it automatically writes the correct ID in the appropriate fields in all of the linked tables.
And if a familiar option is chosen that does not have a unique ID number assigned to it yet, how could I automatically assign it and then for it to be automatically written in the other linked tables.
I split our database on monday and all appeared fine
however today we found that one form will not allow us to enter data
In the table in design view the message is given that the table has fields that cannot be modified.
What has happened here?
All the fields in the form should be locked appart from Date of order and Date of arrival.
In other words - there is no problem pressing the command button and opening the form but we cannot enter data in these 2 feilds which we could do before I split it
Im trying to add 2 new tables to a split and linked database... but no matter which side i add the table to (BE or FE), it wont show up in the linked table manager.. so my question is 1.) is there some trick to getting it to show up in the link manager? or 2.) is there some way to re-merge the database, add the new tables.. and then split it again?
I'd really like some advice on this. I have just finished a database I've been working on (thanks to everyone who helped me) and I just split it so that the tables are on the server and the front end will be on the users machines.
Its a drawings register and many of the users will just need to browse the drawings to see the drawings and revisions that are there. These users will not be able to add or delete records (I presume I do this by setting up a usergroup and permissions?)
They can sort and filter the records in different ways on a datasheet form. They can update only one field in the table and that is the 'selected' field (checkbox on the form). If several users are using it concurrently it will interfere with the drawings that others have selected. From what I understand, I could replicate the tables they would use? Is this the correct way to do this even though the database is split? I'd really appreciate any tips as I've been reading alot on this but am still v unsure..
I have a database that I am about to split and put on a network. Before I do that, I want to attempt the following: If I have users on the database updating or printing reports or something, I want a button or something that I can tell users to get off or just kick users off, without corrupting or damageing the data, so that I can update and/or put new data into the database, then turn it back on for general use. It could be possible that the record that I will update or remove could be in use by a user. so I just want to eliminate the possibility of messing up the database.
thanks in advance....
I've been creating a new Access application and I've run into an issue. The form I created has 2 subforms on it. The data is stored on 3 different tables related by the RMAID. The data gathered from this form needs to go into our MRP application. I have linked the necessary MRP tables to my access application. How do I get the date from the form to the tables in our MRP application? The data needs to go to 3 tables from the MRP application. Do I create an append query that's run after the user completes entering the data on the form? Thanks for any help
I have set up a table (A) that is linked to another table(B) in my database. This linked table (A) is then used in a number of queries. When the data in the original table (A) is updated it does update the data in the linked table (B) as it should. However, when I run the queries they do not bring up any records. It seems that they are not looking at an updated version of the linked table (A) because if I rebuild the query it finds the records as it should. Any advice on this would be very gratefully received.
I need some code to refresh/update linked tables to a data base in the same folder on startup. Any idea how I can do this??
I have a "client/server" split Access 2003 database with an Interface file containing all the code/forms/queries and some system tables and also a Data Backend file, with all the data tables and table structure.
This is being deployed to multiple sites, each getting their own version of the backend, so sites will not be using the same backend between them all, rather multiple users in each site will all share that site's backend file.
Since users will deploy in different network locations, etc. they will have to re-link all tables. Is there a meaningful/user-friendly way of doing that without relying on user's knowledge of "Linked Table manager", etc.?
How do I manipulate data on a split database? I always use CurrentProject.Connection. I'm sure that there is a path type of thing involved.
There are several people who use a database/program that I have created. They each have independent databases on their computers and their computers are not networked. I have "split" the database in order to facilitate updates to the user interface. I now find that I would like extend the functionality of the database/program. This would require the addition of a table to the database/program. Is there a way that I can work on the database on one computer, add the table (and create the appropriate relationships), and then transfer the new table to each of the user's computers without deleting/replacing existing data in their databases?
I have created a simple utility that contains two linked tables to two different networked access databases. The utility runs a couple queries and outputs an excel spreadsheet onto the users desktop.
1. When do the linked tables update? I have experienced missing records, where the linked table isn't showing alll the records from the master. Whats the best way to ensure I'm working with the most updated recordset.?
2. Is there a way to open the spreadsheet automatically once its created without having to reference the Excel Library?
Here's the code I'm using:
DoCmd.TransferSpreadsheet acExport, , "Schedule_Summary_Crosstab", "C:Documents and Settings" & User & "DesktopInspections_Schedule.xls"
DoCmd.TransferSpreadsheet acExport, , "Schedule", "C:Documents and Settings" & User & "DesktopInspections_Schedule.xls"
I have a database with an SQL backend... every time I take this program to another computer I get an odbc failure when trying to access the tables on the server.
the only solution is to use the linked table manager and update the links on all the tables... which means pointing out my ODBC source again.
I want to distribute this app to people who wont know how to do all that stuff, but as it is I cannot.
is there some way to approach automating this process? or perhaps some setting that I need to fix either on the server or on my front end application so that no matter what computer is running the database it will connect just fine
any help would be great
When you have front-end (FE) and back-end (BE) databases, a common problem is re-linking when you move the FE.
To solve that, here is a database with 4 objects:
1. Table -- Syslinks -- lists tablenames to link
2. Form -- Admin_Utilities --has Browse button so you can locate the correct BE file
3. Module -- crystal_TableLink (linking code)
4. Module -- KenGetz_modFileNameDialog (code written by Ken Getz to use the Windows FileOpen dialog box)
all you have to do is import* these 4 objects into your working database and edit the Syslinks table to include all the tablenames you want to link... and compile, of course <g>
once you identify the BE database and Re-Link from the form, the specified tables are linked and their descriptions are updated with the path and name of the BE file
* to import objects:
from the database window, choose File, Get External Data, Import... from the menu
You can also change the BE links by using the following from the menu:
Tools, Database Utilities, Linked table Manager...
(your table descriptions won't be updated with BE path and filename of course)
Is there any way to test to see if access believes that a linked table is or is not update-able?
I've spent the last 2 months designing a database at home with Access 2000. Now finished, I copied the database to a CD, took it to work and transferred it to the machine there which is also running Access 2000. The program works perfectly on my home PC, but the PC at work gives me tons of errors.
After opening the database at work, the switchboard buttons produce an error that states, "Unable to execute this command"
Trying to run Queries gives me one error after the other regarding built-in functions. For example, "Format$()", "Date()" and all other functions are not recognized and I simply get a prompt box that asks me to enter the value for the given function. (As if it's a question for user input) Or, I will simply be prompted that the function name is not recognized.
Points to remember when thinking this one over:
1.) I've made sure that the transferred database is not marked "Read Only"
2.) The sample databases that come with Access 2000 on the work PC run without a problem. Including all functions.
3.) I've saved the database on my home PC in a matching directory name and drive with the work PC. (Although, for some reason this seems like it might still be a problem... Not sure)
4.) The database is split and both parts are stored in the same folder.
If you need more information for this, let me know and I'll post what I can.
Thank you in advance! This one has me going nuts.
Can anyone tell me if this is possible? Please!!
I am trying to create a linked table within Access 2000 and when it asks me for the path, I am tying in the address of my web server Http://www.....and then when I enter my username and password, it shows me the folders within my web space along with the icon for the access database I am trying to connect to. When I double click the database, it does show me the list of tables however it is showing them off my local computer and not the online database. (The databases are exact copies of eachother)
I know this becasue I have entered data in the apparantly linked table and it shows up in the table on the local machine. Access does not seem to like this.
Can anyone suggest how I overcome this at all? Thanks in advance.... :-)
I have the following issue:
The application that we use at my company has limited reporting capabilities. They have given me a tool to create my own, but it can only display information on the screen or export it to a text file. What I've been doing to give my co-workers a more "user friendly" version is to export the file and then have an Access program open that has a table linked to that file.
However, I've been running into the problem lately of multiple users trying to access the same report at the same time. When this happens, they get a message saying: The Microsoft Jet Database engine cannot open the file ". It is already opened exclusively by another user, or you need permission to view its data.
What I'm wondering is if anyone has any ideas on how I can solve this. The only objects in Access are the linked table, a query, and a report. All the users are doing is looking at or printing the report, so there should be no problem with updating records, etc. I'm not sure if this is an Access issue or an issue with the text file, but any help would be greatly appreciated. Thank you!
I am trying to find out if there is any special way I should be compacting a database with linked tables? I deleted a table from my database but it didn't reduce my size and it is still giving me an Invalid Arugment error because it is over 2Gigs. I tried compacting the traditional way, but forgot that having linked tables would make it more difficult then tried to run a make table query but it just sat and clocked...
Can anyone help?
Hi, I have an access database on my web servers which I do not have exclusive access to. Is it possible for me to link tables on an Access database I have on my desktop to the one on the web server via somesort of www. root?
I have a split database that has the back end at a central location and the front ends on local user machines in different parts of the county that see the back end via a VPN .At present it is accessed by about 5 local users. Is there any limit to the number of local users it can handle ?
Hey folks !
I am back again.
I have a linked table through an odbc connection that keeps breaking.
I have a development copy of the database on my desktop and when I copy it out into production after making a fix in it, the odbc call fails.
I can refresh it from my desktop after it has been copied to the network share, but when one of my users tries to get into it again the odbc call fails. BUT if I refresh the link from their pc, it doesn't break again until I patch it again.
I really have no clue how to fix this.
I would like to attempt a refresh on the link when the database opens.
I think that would be the best way to handle it.
Does anyone have any hints or direction that I can go with this?
I have linked tables coming in from 2 databases to my front end.
My database is set up in a manner:
Front End <----- Pricing
Now any time I link Pricing tables to my front end, AND customer tables to my front end(two separate databases)
My Front End locks up for any users(User#2,3,4) except the first one who's currently accessing those two.(User #1)
However, If I Import pricing tables into customers(dbase) first, I can relate the entire "Customers" database to my front end and mutliple users have no problems using their own front end.
Is there a different way I should be doing this? As importing pricing into customers on each change would become annoying once prices are updated frequently.
Thanks, I can clarify a bit further if needed.
I have a need to save off at the end of each month my access friont-end and back-end databases for archiving.
Plan is to place this in a folder with subfolders for each month.
We plan to have one of the users move the "production databases" to the frozen sub-folder.
The users will need to from time to time access and run from these "frozen" databases.
I was wondering if there was a way to automatically rejoin the two databases (front-end & back-end) back into one database within these frozen folders? (My databases are always being modified and so need to keep everything together)
I thought this might be easier than trying to make sure the frozen databases are being linked properly. And since these frozen folders are accessible on the our network, concerned about the issue of possible multiple users accessing a front-end at the same time or of an user copying the frozen database to their machinecand having a issue arise because of the link.
I am coming to the end of the development cycle of my DB and am now attempting to split the DB.
When using the DB Splitting Wizard... I was getting some errors (subscript out of range), so I compiled my code, tried again ...same errors...so now I am going to manually split the DB.
I have created a new Back End (BE) file and imported my tables from MAIN to BE.
However, I have a number of linked tables to another DB. These are now in the BE copy... however, in the DB, I created queries to these tables...(to facilitate easy updating of the linked tables) and these queries are represented in my relationships window.
My question is... should these queries be moved to the BE...what is the "best practice" when doing this. Or should they stay in the MAIN front end?