?
Solved

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

Posted on 2009-03-30
12
Medium Priority
?
301 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 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