I have 50 make table queries that write tables to a back end databsae. The path chang

[GTC07300710]

I have an access database that is split into a front end and a back end database. I have around 50 make table queries that make a table in the back end database.

Currently I have the Another Database radio button checked
Under filename I have referenced a Path and the back end database name.

A user may request the back end database to be placed on a shared server so multiple people can access it. So I have to go into each make table query and manually change the path. This can be cumbersome as I have multiple locations using this.

Looking for a way to change the path once and it changes all the path references in the 50 make table queries. I tried creating a table with the path and then I used a dlookup function in the field. The front end database closed when I did that.

[zmbd]

... I have around 50 make table queries that make a table in the back end database...

OH MY
WHY?!?!
This will lead to bloat like few other things do and suggests that you may need to really look at the table structure for normalization
Bytes > Sitemap > Microsoft Access / VBA Insights>Database Normalization and Table Structures



As for you database closing:
Did you have open, linked tables, or any object bound to them open, to the backend at the time? Changing the path to the link while they are open will usually toss an error; however, there can be strange effects and a frontend crashing is one of the possibilities. Before you can make the change all tables, queries, and forms bound to the linked tables must be closed.

Normally I would suggest using VBA to create a dynamic set of SQL that pulls the path from your backend and then uses the db.execute method. This is what I use when I archive data out of a production database into a subset backend.

Would you mind posting a copy if the SQL you are using?
Of course, you'll want to use generic paths and please make sure that you used the [CODE/] formatting tool

[GTC07300710]

One point of clarification. Each make table queries make a different table in the back end database. 50 make table queries /50 different tables.

SELECT
   SAPMaterialDL1.Plant,
   Replace(LTrim(Replace([SAPMaterialDL1].[Material Number], "0", " ")), " ", "0") AS [Material Number],
   SAPMaterialDL1.[Material Description],
   SAPMaterialDL1.[Material Type],
   SAPMaterialDL1.[Base Unit of Measure],
   SAPMaterialDL1.[MRP Group],
   SAPMaterialDL1.[Plant Status],
   SAPMaterialDL1.[MRP Type],
   SAPMaterialDL1.[Planning Time Fence],
   SAPMaterialDL1.[MRP Controller],
   SAPMaterialDL1.[Lot Size Key],
   SAPMaterialDL1.[Min Lot Size],
   SAPMaterialDL1.[Max Lot Size],
   SAPMaterialDL1.[Assembly Scrap],
   SAPMaterialDL1.[Rounding Value],
   SAPMaterialDL1.[Procurement Type],
   SAPMaterialDL1.[SP Key],
   SAPMaterialDL1.[Planning Calendar],
   SAPMaterialDL1.[SM Key],
   SAPMaterialDL1.[Safety Stock],
   SAPMaterialDL1.[Min Safety Stock],
   SAPMaterialDL1.[Safety Time],
   SAPMaterialDL1.[Safety Time / Coverage],
   SAPMaterialDL1.[Profile for Safety Time],
   SAPMaterialDL1.[REM Allowed],
   SAPMaterialDL1.[REM Profile],
   SAPMaterialDL1.[Plant Deletion Indicator],
   SAPMaterialDL1.[Quality Inspect],
   SAPMaterialDL1.[Storage Bin],
   SAPMaterialDL1.[Old Material Number],
   SAPMaterialDL1.Field31 INTO SAPMaterialDL IN 'C:\Folder\CSSTRbe.accdb' 
FROM
   SAPMaterialDL1;

[zmbd]

Yes, we're aware of what the maketable action query does...

You have 50 of these - so 50 tables once ran.

How are you running the queries?

[twinnyfo]

Why not keep the DB BE on the Server, using the unchanging Network address (e.g., \\ServerName\Directory\Folder\Database\). Thus, the path would never change. This is a principle used in this Article which describes User Permissions in MS Access. Although that DB may be much to chew on, the principle is the same.

[GTC07300710]

I am running the queries through a macro.

[GTC07300710]

Hi twinnyfo,

Each site has their own server so the network address changes.

[Rabbit]

What is the purpose of all this? I suspect you don't actually have to do all of this and there's a much simpler solution in which you just copy and paste a file.

But we can't guide you to a more efficient solution until we know why you need to "make 50 tables" for each site and why each site is constantly changing the location of their back end.

[zmbd]

2nd Rabbit - exactly what I was saying in Post-#2

> You're going to have to look at moving to VBA from the Macro language. Macros are not going to be able to handle building the strings very well and are very difficult to trouble shoot.
I'll have to see if the [Tempvars] collection could be used for the pathname in the action query. Most likely you'll have to directly place it within the SQL view if it's possible.

> Looking at SAPMaterialDL1.[Safety Time / Coverage]
Reccomend removing that slash - it will cause you issues at some point in time - especially with macros!!!!
IMHO, one should only use alphanumerics and the underscore for field names, table names, file names, and other items along these same lines.
Error message when you use special characters in Access databases Applies to: Microsoft Office Access 2007Microsoft Office Access 2003

I highly advise replacing the spaces with the underscore in all of your table designs.
SAPMaterialDL1.[Safety_Time_Coverage],
or
SAPMaterialDL1.[SafetyTimeCoverage],
this will make writing your SQL much simpler.