Solved

How to set report recordsource to table in another access db?

Posted on 2009-03-30
12
275 Views
Last Modified: 2012-05-06
I have a temp db (db2) created in a different location that I need to change the recordsource for a report in db1 to a table located in db2.  What is the best way to do this?
0
Comment
Question by:SDKCompany
  • 6
  • 6
12 Comments
 
LVL 75
ID: 24022327
1) Create a 'Linked' table to that table in DB2

2) You can then directly use that table just like any other table in DB1

mx
0
 
LVL 75
ID: 24022335
To Create a linked table.

1) From the menu ... File>>Get External Data>> Link

Browse to the location of DB2

mx
0
 

Author Comment

by:SDKCompany
ID: 24022353
How do I do that if DB2 and the tables inside of it are only temporary and get deleted after the report runs?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:SDKCompany
ID: 24022538
I think I understand how I can link and use a temp database.  Because the database is created on the users machine, it can have the same name as the databases on other users machines because they are located on the other users machines.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 125 total points
ID: 24022585
Well, in that case you can create a link on the fly in vba code - then delete it when you are done.

To create the link on the fly, you can use

DoCmd.TransferDatabase acLink, "Microsoft Access", "FullPathToDB2", acTable, "LocalTableName", "TableNameInDB2"

LocalTableName and TableNameInDB2  can be the same.

To Delete the link:

DoCmd.DeleteObject acTable, "LocalTableName"
0
 

Author Comment

by:SDKCompany
ID: 24022605
Thats great!  Do you know how I could then delete the temp database when I close?
0
 
LVL 75
ID: 24022703
Use the Kill command - from Help:

Kill Statement
     

Deletes files from a disk.

Syntax

Kill pathname

The required pathname argument is a string expression that specifies one or more file names to be deleted. The pathname may include the directory or folder, and the drive.

Remarks

In Microsoft Windows, Kill supports the use of multiple-character (*) and single-character (?) wildcards to specify multiple files. However, on the Macintosh, these characters are treated as valid file name characters and can't be used as wildcards to specify multiple files.

Since the Macintosh doesn't support the wildcards, use the file type to identify groups of files to delete. You can use the MacID function to specify file type instead of repeating the command with separate file names. For example, the following statement deletes all TEXT files in the current folder.

Kill MacID("TEXT")

If you use the MacID function with Kill in Microsoft Windows, an error occurs.

An error occurs if you try to use Kill to delete an open file.

Note   To delete directories, use the RmDir statement.


mx
0
 

Author Closing Comment

by:SDKCompany
ID: 31564498
You are good.  Everything works just perfect!  Thanks!
0
 
LVL 75
ID: 24022829
"Grade:  A - "You are good.  Everything works just perfect!  Thanks!"  "

You are welcome ...

mx
0
 

Author Comment

by:SDKCompany
ID: 24022924
A final question:  Why can I not use Kill in the close event of a report?  I can use it in the unload event of the form.  Any ideas?
0
 
LVL 75
ID: 24023072
Well ... you will not be able to delete DB2 as long as DB2.LDB exists.  So, in DB1 - as long as you are 'actively' linked to that temp table in DB2 - the LDB will exist.  You will need to remove the link .. OR ... be sure no form or report is open in DB1 that uses the temp table in any way.

mx
0
 

Author Comment

by:SDKCompany
ID: 24023079
OK, that makes sense.  Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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