Solved

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

Posted on 2009-03-30
12
282 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
[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
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
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…

730 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