VBA to ask for file location and use to open DB

[Brian]

Howdy All,

This is part of what I hope to be a large project...

I have created a button, that when press, will prompt the user to browse for
an .mdb file.

I then run a query on that file to bring data into a new worksheet.

When I run it, opens the browse window and I browse and select my database
file, but then I get an error that says:

Could not find file 'C:\DBLocation.mdb'.

This is what I have:

Sub DataImport()


Dim DBLocation As String

DBLocation = Application.GetOpenFilename


ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=DBLocation;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `Combined Data`.`Assignee Mgr Name`, `Combined
Data`.Assignee,
`Combined Data`.`Instance#`, `Combined Data`.`SR Number`, `Combined
Data`.`SR
Reported Date`, `Combined Data`.`Task Number`, `Comb" _
, _
"ined Data`.`Task Actual Start Date`, `Combined Data`.`Task Actual
End
Date`, `Combined Data`.`Debrief Service Month`, `Combined Data`.`Debrief
Status`, `Combined Data`.`Task Type`, `Combined Data`.`Se" _
, _
"rvice Activity Code`, `Combined Data`.`Current Extended Labor
Cost`,
`Combined Data`.`Current Extended Travel Cost`, `Combined Data`.`Current
Extended Standard Cost`, `Combined Data`.`Current Extended" _
, _
" Total Cost`, `Combined Data`.LABOR, `Combined Data`.TRAVEL,
`Combined
Data`.`Ttl Hours`" & Chr(13) & "" & Chr(10) & "FROM `Combined Data`
`Combined
Data`" & Chr(13) & "" & Chr(10) & "WHERE (`Combined Data`.`Assignee Mgr
Name`='KLAVINS, EGONS E JR (ED)')" & Chr(13) & "" & Chr(10) & "" _
, "ORDER BY `Combined Data`.Assignee")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

I'm not sure what I missed...

Thanks,
Brian

 

[Dave Peterson]

DBLocation is a string inside those double quotes:

I'd try:

Dim DBLocation as Variant 'not a string

DBLocation = Application.GetOpenFilename

'just another safety check:
if dblocation = false then
'user hit cancel
exit sub
end if

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & DBLocation & ";Drive...

 

[Dave Peterson]

DBLocation is a string inside those double quotes:

I'd try:

Dim DBLocation as Variant 'not a string

DBLocation = Application.GetOpenFilename

'just another safety check:
if dblocation = false then
'user hit cancel
exit sub
end if

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & DBLocation & ";Drive...

 

[Brian]

Again Dave, Thanks a Million!

I had tried " & DBLocation & ", but with DBLocation as a string, I'm
guessing that Variant made the difference?

I hope you don't mind what are likely to be additional questions as my
project progresses.

Brian

 

[Brian]

Again Dave, Thanks a Million!

I had tried " & DBLocation & ", but with DBLocation as a string, I'm
guessing that Variant made the difference?

I hope you don't mind what are likely to be additional questions as my
project progresses.

Brian

 

[Dave Peterson]

The only time that the declaration would make a difference is if the user hit
cancel--and then your code would have to either check that.

Or the code would be trying to open a file named DBLocation (as that string)
or a file named False if you used "...& dblocation &..."

You may want to start asking programming questions in the .programming
newsgroup. Most of the regulars read the major newsgroups, but it makes more
sense down the hall a bit.

 

[Dave Peterson]

The only time that the declaration would make a difference is if the user hit
cancel--and then your code would have to either check that.

Or the code would be trying to open a file named DBLocation (as that string)
or a file named False if you used "...& dblocation &..."

You may want to start asking programming questions in the .programming
newsgroup. Most of the regulars read the major newsgroups, but it makes more
sense down the hall a bit.

 

[Dave Peterson]

Just to clarify...

Dim FName as string
fname = application.getopenfilename(...)
if fname = "False" then

vs

Dim FName as Variant
fname = application.getopenfilename(...)
if fname = False then

 

[Dave Peterson]

Just to clarify...

Dim FName as string
fname = application.getopenfilename(...)
if fname = "False" then

vs

Dim FName as Variant
fname = application.getopenfilename(...)
if fname = False then