Newbie Issue: I can't get my variable to work here. . .please help.

[KellyStee]

OK, I'm a newbie when it comes to writing VB code so this might be a very stupid question. I got the code below from MSDN and all I want to do is instead of hardcoding the path (as seen below), I want to have a box that prompts you for the path and then accepts the path you enter. I've set up the input box and variable and it gives me an error when I try to run it. Any suggestions?
Also, this might be too advanced for me. . .is there a way to have the input box allow you to "browse" for the file?
-------------------------------------------------
Option Explicit
Function LinkSchema()
Dim db As DAO.DATABASE, tbl As DAO.TableDef
Dim strPath As String

Set db = CurrentDb()
Set tbl = db.CreateTableDef("Imported Data&quot
'strPath = InputBox(Prompt:="Please enter the path of the file you are importing:", _
' Title:="Path", XPos:=2000, YPos:=2000)
tbl.Connect = "Text;DATABASE=C:\TestData;TABLE=Extract.txt"
'I can't seem to stick a parameter above such as:
tbl.Connect = "Text;DATABASE=strPath;TABLE=strTable"
tbl.SourceTableName = "Extract.txt"
db.TableDefs.Append tbl
db.TableDefs.Refresh
End Function

 

[chrissie1]

Function LinkSchema()
Dim db As DAO.DATABASE, tbl As DAO.TableDef
Dim strPath As String

Set db = CurrentDb()
Set tbl = db.CreateTableDef("Imported Data&quot
strPath = InputBox(Prompt:="Please enter the path of the file you are importing:", _
Title:="Path", XPos:=2000, YPos:=2000)
tbl.Connect = "Text;DATABASE= " & strPath & ";TABLE=" & strTable
tbl.SourceTableName = "Extract.txt"
db.TableDefs.Append tbl
db.TableDefs.Refresh
End Function "What a wonderfull world" - Louis armstrong

 

[chrissie1]

if you want to open the common dialog boxes (open/save) then try this code but its difficult for a beginner

http://www.mvps.org/access/api/api0001.htm

"What a wonderfull world" - Louis armstrong

 

[KornGeek]

I'm not sure of the syntax, but you can try

tbl.Connect = "Text;DATABASE=" & strPath & ";TABLE=" & strTable

or possibly

tbl.Connect = "Text;DATABASE='" & strPath & "';TABLE='" & strTable & "'"

Sleep is for people with no caffeine.