?
Solved

front end database needs FrontEndTable with local copy of BackEndLinkedTable structure

Posted on 2011-02-10
9
Medium Priority
?
328 Views
Last Modified: 2012-08-13
In this application, each client computer needs a work area that holds data until it is ready to be posted to the shared database.

I used the following sql code to create it.

DoCmd.DeleteObject acTable, "FrontEndTable"
docmd.runsql "SELECT TOP 1 * INTO FrontEndTable FROM BackEndLinkedTable"

 I just ignore the single extra record that was created - it does not cause any problems.

But, what if I want the indexes to also be copied to the new table?  I tried the following, but it did not work because it simply created a duplicate link.

Any idea?

DoCmd.DeleteObject acTable, "FrontEndTable"
strNewTable = "FrontEndTable"  'or pull it from anywhere
strOldTable = "BackEndLinkedTable"  'or pull it from anywhere
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, strOldTable, strNewTable, True
End Sub
0
Comment
Question by:rberke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 34866668
Use acImport instead of acExport and specify the path to the back-end database:

DoCmd.TransferDatabase acExport, "Microsoft Access", strBackEndPath, acTable, strOldTable, strNewTable, True

--
Graham Mandeno - Access MVP
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 1000 total points
ID: 34866679
Oops!  I copied and pasted your code and didn't change everything as intended:

DoCmd.TransferDatabase acImport, "Microsoft Access", strBackEndPath, acTable, strOldTable, strNewTable, True
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 34866881
You could also think about creating your duplicate table in the front end, With all your indexes and data types etc, and then instead of Deleting the table and re-creating it each time, simply use a delete query to delete existing records and an append query to append the new ones from your linked table.

Leigh
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 34867815
Yes, I agree with Leigh about creating the table only once.  Furthermore, I would recommend that you do not create the "local" table in the front-end, because adding and deleting many records over time will bloat your front-end and make it less reliable and more prone to corruption.

Instead, create a local back-end with the duplicate tables as required and link them to the front-end.  You can then move records between the local and shared tables just as easily as if the local tables were resident in the front-end.

To delete all (or some) records from a table, you don't need to create and save a delete query.  Simply execute a SQL delete statement from VBA:

CurrentDb.Execute "Delete * from [your local table]"

--
Graham
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 34867973
As Graham suggest's creating the table in the back end is more desireable, however if for some reason the table needs to be in the front end ie, Network issues, Take Offsite, Performance issues etc I would recommend setting your DB to "Compact On Close" to ease the bloating issue that Graham mentioned.

Leigh
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 34868090
Sorry - perhaps I didn't explain myself clearly...

I didn't mean to suggest you create the "work area" tables in THE back-end.  What I meant was that you should have two back-ends - one on the shared location on the server, and one on the local hard drive.  Link the tables from both back-ends to your front-end.

Create the "work area" tables in the local back-end, along with all the required indices and relationships, and use SQL statements (Delete, Update, and Insert into) to move records between the two back-ends as required.

--
Graham
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 34868105
OIC,

Sorry you did explain correctly,  I just miss interpereted.

Still, Regular compacts on both back ends would still be advisable.

Leigh
0
 
LVL 5

Author Comment

by:rberke
ID: 34874500
Thanks, that worked fine:

On Error Resume Next
DoCmd.DeleteObject acTable, "FrontEndTable"
On Error GoTo 0
strNewTable = "FrontEndTable"  'or pull it from anywhere
strOldTable = "BackEndLinkedTable"  'or pull it from anywhere
strBackEndPath = "S:\my program files\Back End Billing.mdb"
DoCmd.TransferDatabase acImport, "Microsoft Access", strBackEndPath, acTable, strOldTable, strNewTable, True

And your suggestion about putting it on the backend etc don't really help in this situation.  
From past experience, when a front end table is entirely temporary, my approach of creating a local copy is really the best - no conflict with other users, no access to the network etc.

And, my front ends never get bloated because the user logon procedure deletes the local MDB and refreshes it from the server.

Just a few more details if anybody is interested.

#1 user pushes an access button and my program does the following
   #1a  goes to the government website and downloads a webpage with data
   #1b  process that webpage and extracts information to a temporary FrontEndTable.
   #1d  performs integrity checks to insure that the FrontEndTable in a "clean" status.
   #1e  if and only if the temporary table is clean, the data user is given an option to upload the file to the BackEndLinkedTable
 
The programs are being changed frequently, and new columns are being added to BackEndLinkedTable almost every week which is why I like to clone the structure everytime the program is run.

In this particular situation, there are no advantages to having the clone be in the back end.
 

 
0
 
LVL 5

Author Closing Comment

by:rberke
ID: 34874554
Upon rereading, on noticed that Graham was suggesting a second back-end that would be on the local drive.  Which is almost identical to my approach.  But, it would not work well in my situation for some funky technical reasons.

rberke
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question