VBA Macro create Link in Access Db

[jmoney30]

Hi,
I have over 100 DB in a file path and I'm trying to create a link from one DB. It will be impractical to go thru the link table wizard so I tried this code below. I'm not recieved an error but it doesn't loop thru the directory or it doesn't create all the link. What is missing?

Thanks

Sub LinkAllTblsinDir()

Dim sTblNm As String

Dim sPath As String

Dim sFileNm As String


sPath = "C:\Users\"

'Turn of the Echo to avoid window repaint/refresh

Application.Echo False

sFileNm = Dir(sPath, vbNormal)

Do While sFileNm <> ""

If Right(sFileNm, 3) = “mdb” Then

'sTblNm = Left(sFileNm, Len(sFileNm), 4) 'Extract the file name

sTblNm = Left(sFileNm, 4)

'Use the TransferDatabase option to link the tables from the specified directory

'to your current Access DB

DoCmd.TransferDatabase acLink, "member_combine", sPath, acTable, sTblNm, sTblNm

End If

sFileNm = Dir

Loop



Application.Echo True

End Sub

 

[Micron]

Don't know why you're not seeing any error messages but your transfer syntax doesn't look right. Here's what seems wrong to me:
The database type parameter doesn't look acceptable;
you're providing a path where it expects a name;
you are providing part of the db name for the name of the object (table) that you want to link to.

See DoCmd.TransferDatabase method (Access)

 

[jmoney30]

I updated the code below. I receive no errors. It starts and then after 30 secs it goes into “not responding “. It seems like it is in an never ending loop. Not sure what needs to be tweak now.
Sub LinkAllTblsinDir()

Dim sTblNm As String

Dim sTblNm2 As String

Dim sPath As String

Dim sFileNm As String



sPath = "C:\Users\test2\"

'Turn of the Echo to avoid window repaint/refresh

Application.Echo False

sFileNm = Dir(sPath, vbNormal)

'sFileNm = Dir(sPath & "*.mdb")

Do While sFileNm <> ""

If Right(sFileNm, 3) = “v” Then

'If InStr(sFileNm, "v2k") Then

'sTblNm = Left(sFileNm, Len(sFileNm), 4) 'Extract the file name

sTblNm = "M"

sTblNm2 = Left(sFileNm, 4)

'Use the TransferDatabase option to link the tables from the specified directory

'to your current Access DB

DoCmd.TransferDatabase acLink, "Microsoft Access", sPath, acTable, sTblNm, sTblNm2

End If



'sFileNm = Dir



Loop



Application.Echo True

End Sub

 

[Micron]

Put a break point at the starting point of the code and cause it to run. Then repeatedly press F8 to step through and see what happens. You might spot that it's caught in a never ending loop. While stepping through, you can also check the values of variables, and sometimes references, by mousing over them.

IMO, you need to put code, properly formatted and indented, within vba code tags (see posting toolbar).
After over 10,000 posts in Access & vba forums, I find it easier to pass over code posts like that as opposed to trying to decipher them looking for issues.

 

[jmoney30]

When I step thru, it finds the path and file name. However it says invalid file name. I also changed the If statement to If InStr(sFileNm, “v2k”). What’s strange if I write the direct path with file name and ext it works. However I’m trying to do a loop. Thanks

 

[jmoney30]

I figured it out the sPath was only showing the path with no file name. Once I concatenated the sPath with the loop sFileNm it worked. I thought the Dir is supposed to do join the two together.
Thanks

 

[Micron]

Congrats on finding and fixing the problem by yourself! I hope you learned a troubleshooting technique there that you can use next time.