Data source to equal input box value

Question

I have an Excel file that updates my Access database, however I realised the Access database regularly changes location so I would like the file location to be inputted by an input box and this used. I've tried case select but this hasn't been successful, here's what I have so far:

d = InputBox(Prompt:="Enter file path", Title:="Please enter the file path", Default:="File Location")
Select Case StrPtr(d)
Case 0

Exit Sub
Case Else
Dim cnn As Object
Dim lngRow As Long
Dim lngID As Long, LR As Long, Upd As Long
Dim strID As String

LR = ThisWorkbook.Worksheets("Update").Range("BN" & Rows.Count).End(xlUp).Row
Upd = LR - 1
lngRow = 2

Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=d"

Could try a native file dialogue window http://www.wiseowl.co.uk/blog/s209/type-filedialog.htm http://stackoverflow.com/questions/14915179/ms-access-browse-for-file-and-get-file-name-and-path — Matt Hall, Oct 22 '15 at 13:54
Yep, agree, file dialogue might be better. In any event change last line to `..."Data Source=" & d` — Ambie, Oct 22 '15 at 13:55

score 2 · Answer 1 · answered Oct 22 '15 at 14:25

I would prefer to use the FileDialog, because it is better at preventing invalid input. However, if you want to just use an input box, you can use the Dir function to validate that it resolves to an existing file path.

d = InputBox(Prompt:="Enter file path", Title:="Please enter the file path", Default:="File Location")
If Dir(d) = vbNullString
    'This is not a valid path, you might want to show a messagebox.
    Exit Sub
End If

Data source to equal input box value

1 Answers1