Attached is code to link tables in databases that are in a folder. My variable for the source in the DoCmd.transferDatabase code Dir(strPath & rst!FolderName) is not working right. I want to loop through the db names to link the tables.
each db has only 1 table which is named the same as the db. We have a macro that pulls all the db names from the folder into a table called tblDirectory into the field FolderName (that's a bit misleading, I know). I have very little VB knowledge. I'd appreciate any detailed instructions. -
Function LinkTable()
-
-
Dim rst As DAO.Recordset
-
Dim strPath As String
-
Dim strFile As String
-
-
strPath = "D:\Temp\Testing\"
-
-
Set rst = CurrentDb.OpenRecordset("tblDirectory", dbOpenTable)
-
Do Until rst.EOF
-
'ensure exist
-
strFile = Dir(strPath & rst!FolderName)
-
If Len(Dir(strPath & rst!FolderName)) > 0 Then
-
DoCmd.TransferDatabase acLink, "Microsoft Access", Dir(strPath & rst!FolderName), acTable, rst!FolderName, rst!FolderName, False
-
End If
-
rst.MoveNext
-
Loop
-
rst.Close
-
Set rst = Nothing
-
-
End Function
-
The picture is getting clearer. I would guess the File is being found since you are getting past line 13 which is testing for it's existence. I should have realized that if that was working, the file extension is included in rst!FolderName.
So since the Extension is in FolderName, it should be stripped out when referring to the tables. You might want to try this for line 14: - DoCmd.TransferDatabase acLink, "Microsoft Access", strPath & rst!FolderName, acTable, Left(rst!FolderName, InStr(rst!FolderName, ".") - 1), Left(rst!FolderName, InStr(rst!FolderName, ".") - 1), False
If it works, it might be best to go back and clean it up by putting table name into a string of it's own.
10 7938
I didn't see anything glaring obviously wrong with the code, so it would be nice to know how it is not working right. What error are you getting?
access says, "error 3024, cannot find the file..." and displays the path of my default folder (the documents folder) and the name of the first db in the list. thank you for looking.
after clicking debug access stops on the line DoCmd.TransferDatabase acLink, "Microsoft Access", Dir("D:\Temp\Testing\" & rst!FolderName) etc
I think line 14 should be: - DoCmd.TransferDatabase acLink, "Microsoft Access", strPath & rst!FolderName, acTable, rst!FolderName, rst!FolderName, False
-
unfortunately that did not do it. I get a message that access database engine could not find the object... and lists the first db name in the table. here's the current code: -
Function LinkTable()
-
-
Dim rst As DAO.Recordset
-
Dim strPath As String
-
Dim strFile As String
-
-
strPath = "D:\Temp\Testing\"
-
-
Set rst = CurrentDb.OpenRecordset("tblDirectory", dbOpenTable)
-
Do Until rst.EOF
-
'ensure exist
-
strFile = Dir(strPath & rst!FolderName)
-
If Len(Dir(strPath & rst!FolderName)) > 0 Then
-
DoCmd.TransferDatabase acLink, "Microsoft Access", strPath & rst!FolderName, acTable, rst!FolderName, rst!FolderName, False
-
'DoCmd.TransferDatabase acLink, "Microsoft Access", Dir("D:\Temp\Testing\" & rst!FolderName), acTable, rst!FolderName, rst!FolderName, False
-
End If
-
rst.MoveNext
-
Loop
-
rst.Close
-
Set rst = Nothing
-
End Function
-
Here is the examples from Microsoft on the - DoCmd.TransferDatabase:
-
DoCmd.TransferDatabase acImport, "Microsoft Access", _
-
"C:\My Documents\NWSales.mdb", acReport, "NW Sales for April", _
-
"Corporate Sales for April"
-
-
DoCmd.TransferDatabase acLink, "ODBC Database", _
-
"ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
-
& "DATABASE=pubs", acTable, "Authors", "dboAuthors"
When they refer to an Access database the full Database Name is listed, including “.MDB”. I have a feeling that may need to be included as well. So maybe Line 14 should be: - DoCmd.TransferDatabase acLink, "Microsoft Access", strPath & rst!FolderName & “.mdb”, acTable, rst!FolderName, rst!FolderName, False
-
thanks again. the .accdb is included in the table (see below):
FolderName
AUTH.accdb
MONTHS.accdb
PHASE.accdb
ACCT_LST.accdb
actually, access tells me the first table name, it says it can't find the folder. so it's having problems finding the actual location
The picture is getting clearer. I would guess the File is being found since you are getting past line 13 which is testing for it's existence. I should have realized that if that was working, the file extension is included in rst!FolderName.
So since the Extension is in FolderName, it should be stripped out when referring to the tables. You might want to try this for line 14: - DoCmd.TransferDatabase acLink, "Microsoft Access", strPath & rst!FolderName, acTable, Left(rst!FolderName, InStr(rst!FolderName, ".") - 1), Left(rst!FolderName, InStr(rst!FolderName, ".") - 1), False
If it works, it might be best to go back and clean it up by putting table name into a string of it's own.
wooohooo!! thank you!!! worked!!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: dixie |
last post by:
I am using the Docmd.TransferDatabase to transfer part of a table into
another smaller database. The source comes from a select query that
transfers the part of the table that I need.
The...
|
by: AccessStarters |
last post by:
I am trying to Import a simple dbase file into my Ak2 TABLES.
If I do this manually, it works fine. I get a new table called DBASE with
all the data looking good.
If I try and do this using VBA...
|
by: astro |
last post by:
I'm trying to copy some ODBC linked tables from one access2k mdb to another.
This is an example of the code i'm using:
DoCmd.TransferDatabase acExport, "Microsoft Access", (strPath &
strTemp),...
|
by: clusardi2k |
last post by:
In chapter 23, it explains how to navigate the tables of a DataSet. I'm
looking for the definition of "AllOrders1", below. I.E.: How is it
created, explained to me in a step by step fashion, and...
|
by: Minion |
last post by:
I hate to even post this message as I probably know the answer before I begin. Still there are several problems facing me with this particular challenge.
First though I'll goto into the overview...
|
by: amitkumar19 |
last post by:
hello dear
when i use this statements in vb6 for transfer data to one another
DoCmd.TransferDatabase acImport, "dBase III", "C:\Data", acTable, _
"Test112A.dbf", "TempFile"
then give a error...
|
by: amitkumar19 |
last post by:
hello dear
when i use this statements in vb6 for transfer data to one another
DoCmd.TransferDatabase acImport, "dBase III", "C:\Data", acTable, _
"Test112A.dbf", "TempFile"
then give a error...
|
by: chris.spano |
last post by:
I'm running Access 2003 on an XP machine, and there are multiple
developers who are working on different "assignments" of our large
database project. We all work on a local copy and export changes...
|
by: ncsthbell |
last post by:
I am using MS2007 on windows XP. I support an access application which has a 'main menu' (form) and on this form there is a button 'Exit Database'. In the 'on click' event code for the 'Exit...
|
by: albertob |
last post by:
Hello!
I have the following problem. I'm trying to use an ODBC connection to import one table from an ORACLE database to my access database.
Manually, trough access command panel database ODBC,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |