By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,027 Members | 1,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,027 IT Pros & Developers. It's quick & easy.

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

P: 4
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.
Jun 18 '19 #1
Share this Question
Share on Google+
8 Replies


zmbd
Expert Mod 5K+
P: 5,397
... 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
Jun 18 '19 #2

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

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.    SAPMaterialDL1.Plant,
  3.    Replace(LTrim(Replace([SAPMaterialDL1].[Material Number], "0", " ")), " ", "0") AS [Material Number],
  4.    SAPMaterialDL1.[Material Description],
  5.    SAPMaterialDL1.[Material Type],
  6.    SAPMaterialDL1.[Base Unit of Measure],
  7.    SAPMaterialDL1.[MRP Group],
  8.    SAPMaterialDL1.[Plant Status],
  9.    SAPMaterialDL1.[MRP Type],
  10.    SAPMaterialDL1.[Planning Time Fence],
  11.    SAPMaterialDL1.[MRP Controller],
  12.    SAPMaterialDL1.[Lot Size Key],
  13.    SAPMaterialDL1.[Min Lot Size],
  14.    SAPMaterialDL1.[Max Lot Size],
  15.    SAPMaterialDL1.[Assembly Scrap],
  16.    SAPMaterialDL1.[Rounding Value],
  17.    SAPMaterialDL1.[Procurement Type],
  18.    SAPMaterialDL1.[SP Key],
  19.    SAPMaterialDL1.[Planning Calendar],
  20.    SAPMaterialDL1.[SM Key],
  21.    SAPMaterialDL1.[Safety Stock],
  22.    SAPMaterialDL1.[Min Safety Stock],
  23.    SAPMaterialDL1.[Safety Time],
  24.    SAPMaterialDL1.[Safety Time / Coverage],
  25.    SAPMaterialDL1.[Profile for Safety Time],
  26.    SAPMaterialDL1.[REM Allowed],
  27.    SAPMaterialDL1.[REM Profile],
  28.    SAPMaterialDL1.[Plant Deletion Indicator],
  29.    SAPMaterialDL1.[Quality Inspect],
  30.    SAPMaterialDL1.[Storage Bin],
  31.    SAPMaterialDL1.[Old Material Number],
  32.    SAPMaterialDL1.Field31 INTO SAPMaterialDL IN 'C:\Folder\CSSTRbe.accdb' 
  33. FROM
  34.    SAPMaterialDL1;
Jun 18 '19 #3

zmbd
Expert Mod 5K+
P: 5,397
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?
Jun 19 '19 #4

twinnyfo
Expert Mod 2.5K+
P: 3,284
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.
Jun 19 '19 #5

P: 4
I am running the queries through a macro.
Jun 20 '19 #6

P: 4
Hi twinnyfo,

Each site has their own server so the network address changes.
Jun 20 '19 #7

Rabbit
Expert Mod 10K+
P: 12,366
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.
Jun 20 '19 #8

zmbd
Expert Mod 5K+
P: 5,397
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.
Jun 20 '19 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Browse more Microsoft Access / VBA Questions on Bytes