Solved

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

Posted on 2009-03-30
12
265 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
word 2016 1 29
Use a Combo-Box to do a Search 26 23
Access 2016 - Labels prompt to print 4 30
Launch app from Access custom ribbon 8 32
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

813 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

9 Experts available now in Live!

Get 1:1 Help Now