Creating New "Back End" Tables Daily

[jercsr]

I have a question about linking "Front End" objects, specifically to constantly changing Back End tables. Following is a description of how I've set things up:

I export data from an application, which creates a new MDB each time. The utility to export the data automatically creates a MDB with the name and in the location that I specify, with a table called MAIN. From there, I have created a "Front End" MDB with a Queries and Reports that obtain records from the linked table MAIN in the "Back End" MDB.

My problem is this: The data that exports to this back end table is project specific (coming from project-specific AutoCAD drawings). I need a way create a new "Back End" MDB in each project directory, and at the same time have a copy of the "Front End" MDB in the same project directory, so that the link will automatically occur between both the front and back end and the records will be project specific.

Should I be using "relative paths" in my table link? Is there code that will have the Front End MDB search for the Back End MDB in the current directory?

Example: I do not want the "Front End".mdb in project #161 to get records from table MAIN in the "Back End".mdb under project #171.

Thanks

Jeremy

 

[KenReay]

Hi

I have written similar systems in project specific environments.

The path of linked tables is 'hard' ie does not use relative paths, but you can find the path of the current application (front end) so theer is no reason why you should not on openning the front end check for broken links and then relink the tables as necessary, if you folder names are consistent and contain teh project Id you could even write code to parse out the project Id and double check the table content of the backend.

BUT, does this approach not give you a front end maintenance problem (ie you have 'n' copies of the front end. Would it not be best to have a single pront end, which on entry prompted user for the Project Id, then relinked the tables as necessary to point to the relevant backend?

If you need advice on how to do any of the above it would be helpful to know the version of Access and if you are using DAO or ADO to access your tables.


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[DonQuichote]

There is no such thing as a relative path in table links (internally in Access). You could show a file selector to select or create a new mdb. I you create a new backend, you can create the necessary tables in it as well.

Then you remove the old links from the tabledefs collection in your front-end. You can now re-create the links, but to the selected backend.

Best regards

 

[jercsr]

Thanks both to Ken and DonQuichote for your helpful responses.

Just for a moment, I wanted to touch on what Ken said:

I would love to be able to prompt a user to enter a project number, and pass that information so that the report goes out and "finds" the necessary table.

It would be ideal if the data extracted from the AutoCAD application would append records to a constantly growing table so that I could query specific records using a LOCATION field, but my extraction tool creates a new database each time. So any advice you may have on creating a "dynamic link" based on a user's input would be much appreciated. I have simply been using Access 2002 to create the reports. From there I was simply going to place a shortcut in a folder to open the report. I don't know if this is DAO or ADO (unsure about these abbreviations).

Thanks again ,

Jeremy

 

[JeremyNYC]

Jeremy,

What about creating code that imports from the newly created back end into a master back end? You'd have to link to the new back end and append the records to the existing table, assigning a value to your Location field. That way you'd end up with all your data in one table, which, as you point out, would be a far better solution.

This code oculd be fired a number of ways. Certainly you could drop a button somewhere to do it manually. Possibly you could run the code in the process that creates the database. You could also check for the existence of a new database anytime a user starts the main application, appending the records and then moving the database to a new location, so the append only happens once (actually, it might be better to move it first, so that you don't run into a situation where more than one person appends the records at the same time).

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[jercsr]

Thanks, JeremyNYC. I'm going to experiment with that. By the way, I've seen your expert posts elsewhere, and I'm wondering if you have a comment regarding my question in thread700-658406.

Thanks again!