Linking tables via an InputBox

kupe

Registered User.
Local time
Today, 23:42
Joined
Jan 16, 2003
Messages
462
I want to link to tables in a database. If I state which database WITHIN THE CODE, as in

Private Sub Command0_Click()

DoCmd.TransferDatabase acLink, "Microsoft Access", "T:\www.bemrosebooth.com\OnLine_Marketing\Link Building\Link Survey databases\SmartCards.mdb", acTable, "tblDeleteMe", False

End Sub

it works well.

BUT I would like to use an InputBox to name the database I want to link. This code is almost the same as above,

Private Sub Command1_Click()

Dim stDocName As String
Dim strfilename As String

strfilename = InputBox("What is the name of the database you'd like to link to?")

DoCmd.TransferDatabase acLink, "Microsoft Access", "T:\www.bemrosebooth.com\OnLine_Marketing\Link Building\Link Survey databases\" & "strfilename" & ".mdb", acTable, "tblDeleteMe"

End Sub

Yet it won't oblige. "Can't find the database file," a message box says. And it gives this path, 'T:\www.bemrosebooth.com\OnLine_Marketing\Link Building\Link Survey databases\strfilename.mdb.

So somehow, Access misunderstands the InputBox arrangement. Be very pleased if anyone can spot my mistake.

Cheers
 
DoCmd.TransferDatabase acLink, "Microsoft Access", "T:\www.bemrosebooth.com\OnLine_Marketing\Link Building\Link Survey databases\" & "strfilename" & ".mdb", acTable, "tblDeleteMe"
Syntax error there. Should be no quotes around the strfilename as
& "strfilename" & looks for strfilename
& strfilename & looks for what is contained in the variable strfilename

therefore try

DoCmd.TransferDatabase acLink, "Microsoft Access", "T:\www.bemrosebooth.com\OnLine_Marketing\Link Building\Link Survey databases\" & strfilename & ".mdb", acTable, "tblDeleteMe"
 
Nearly there, thanks Fizzio. But now I get

error 3125
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

Any thoughts please?
 
The code should be ok except if the Database name has punctuation or special characters in it or you enter a null value.
Does the debug stop on this line?
 
No, no special characters or punctuation. Databases I'm using have the names CarParking and Forms, so I wouldn't expect problems there.
Yes, it stops on that line.
 
Just out of interest, when the debug highlights the line, what value is currently showing in strfilename? (hover the mouse over the strfilename)
 
That's interesting and new for me. Thank you.

It says strfilename = "Forms". (I didn't put quotes round the word Forms in the InputBox, by the way.)
 
OK, so access is picking up the correct string from the inputbox.

Try this then.

DoCmd.TransferDatabase (acLink, "Microsoft Access", "T:\www.bemrosebooth.com\OnLine_Marketing\Link Building\Link Survey databases\" & strfilename & ".mdb", acTable, "tblDeleteMe")

If no joy, double check the syntax on your TransferDatabase Line.
 
Sadly, it won't accept the brackets.

Oddly, if I put quotes round the filename in the InputBox, then it actually names the database in the error message. If I hover the cursor over strfilename in the path, however, it gives the database name, Forms, double quotes, as in ""Forms"".

I'll take it with me and try it on my laptop. Very grateful for all the input, Fizzio.

(I've wondered for months why a fizzio would be coding like this. Though perhaps you went from database manipulation into body manipulation. The latter might be less frustrating, perhaps.)
 
Hi,

Let me break into the discussion...
while the pad to your database has spaces in it, I'd think this will work well:
Code:
DoCmd.TransferDatabase acLink, "Microsoft Access", _ 
"T:\[url]www.bemrosebooth.com\OnLine_Marketing\Link[/url] Building\Link Survey databases\" & _
Chr(34) & strfilename & Chr(34) & ".mdb", _
acTable, "tblDeleteMe"
Copy this straight into your code (the underscore is to break the lines)

Luck!
 
Just going out the door. Will try it over the weekend. Many thanks.
 
(I've wondered for months why a fizzio would be coding like this. Though perhaps you went from database manipulation into body manipulation. The latter might be less frustrating, perhaps.)

The Db thing is a hobby of sorts. The body manipulation is the real day job and always has been (just like Rich is;) ) and is still very enjoyable The Db thing came later when the A2k bug thankfully ate our Paradox system and I was 'asked nicely' to do something about it.
 
Rushed back and tried it. It's even closer to the ultimate because at least the error message shows the database name in the path now. But ... it won't oblige. Some weekend fiddling for me then. Much obliged, Bert.
 
Cheers, Fizzio. Curiosity mostly satisfied, thanks. You certainly got yourself well trained in VB coding. It's very impressive. All the best for the weekend. Cheers.
 

Users who are viewing this thread

Back
Top Bottom