Solved

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

Posted on 2009-03-30
12
244 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
 

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 - Access MVP) 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now