Printable View
I am trying to create a dsn-less connection to an access database. I dont want to hard code the path of the database either. I want to use the windows registery to connect to the last db that was accessed by this application. If run for the first time then i want to be able to pop a dialog that opens in a current dir to search for "xxx.mdb" file. How can i do this??
Your request is rather complex, but is do-able, AND, you may want to think it out rather well, because this option is something you will want to use with many other applications.
1) Create a form that requests the location of your database in a directory and get the name of it. If you decide to hard code the database name then use this technique to just get the folder...
This is all the code in my Sub Main to get the information and if it doesn't exist in the PathFilename Property of the frmDBLoc form, then it opens the form and asks for the information.This is the complete set of code that goes in the frmDBLoc form. It's quite simple and does exactly what I want.Code:' this code goes in Sub Main
strDBPath = frmDBLoc.PathFilename
If Len(strDBPath) = 0 Then
strDBPath = PathSlash(App.Path) & "xxx.MDB"
End If
If Not FileExists(strDBPath) Then
frmMessage.CloseForm
frmDBLoc.Setup vbModal, strDBPath
End If
' This goes on the frmDBLoc form I created to ask for the
' path and name of the database
Option Explicit
Public Cancel As Boolean
Private mPathFilename As String
Private Sub CancelButton_Click()
Cancel = True
Unload Me
End Sub
Private Sub OKButton_Click()
If Not FileExists(Me.txtDBLoc.Text) Then
If MsgBox("The file doesn't exist, do you want _
to set this path and filename anyway?", _
vbQuestion + vbYesNo + vbDefaultButton2) _
= vbNo Then
Exit Sub
End If
End If
PathFilename = Me.txtDBLoc.Text
Cancel = False
Unload Me
End Sub
Public Property Get PathFilename() As String
PathFilename = GetSetting(App.Title, "Options", "DBLoc", VBZLString)
End Property
Public Property Let PathFilename(ByVal PathFilename As String)
SaveSetting App.Title, "Options", "DBLoc", PathFilename
End Property
Public Sub Setup(ShowModal As FormShowConstants, Optional ByVal Def_PathFilename As String)
If Len(Def_PathFilename) > 0 Then
Me.txtDBLoc.Text = LCase(Def_PathFilename)
End If
On Error Resume Next
Me.Show ShowModal
If Err Then
Me.Show
End If
Err.Clear
End Sub
It incorporates a degree of OOP, but could be enhanced quite a bit.
Enjoy!!
A couple things come to mind though.
1) What does the function PathSlash() do?? From what i understand it is a user-defined function and im not sure how you coded it.
2) You created a string variable mPathFilename but did not use it anywhere. Is this by error??
3) Where would i define my recordset in this application. For instance, what would the active connection (rs.activeconnection) property be for this code.
Here is the DB connection part that i was previously using. Its all in the Sub Main() ----->
Dim X As clsFlorist
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'clear collection
For i% = 1 To bookcollection.count
bookcollection.Remove 1
Next i%
cn.Open "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DBQ=S:\WINGS FLORIST LOOKUP\Wings DB.mdb;DefaultDir=S:\WINGS FLORIST LOOKUP;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;FILEDSN=S:\WINGS FLORIST LOOKUP\Florist Search DSN\WingsDSN.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=adm in;UserCommitSync=Yes;"
rs.ActiveConnection = strDBPath
rs.Source = "select * from [wings orders]"
rs.Open
rs.MoveFirst
Thank you in advance.
sorry forgot to change in the above code....
rs.ActiveConnection = cn NOT strDBPath
I was trying to test that out
Sorry for leaving some things out, they were insignificant, but I can see that sometimes it causes for confusions. Will do better next time ... here goes.
A couple things come to mind though.
-------------------
Q: What does the function PathSlash() do?? From what i understand it is a user-defined function and im not sure how you coded it.
A: You're right PathSlash is a user-defined function and it simply checks to see if a "\" is at the end of the path, and adds it if it's not. Very simple, so I didn't add it.
-------------------
Q: You created a string variable mPathFilename but did not use it anywhere. Is this by error??
A: No, it's not an error, I didn't clean it out. Sorry. I will update my code. Thanks.
-------------------
Q: Where would i define my recordset in this application. For instance, what would the active connection (rs.activeconnection) property be for this code.
A: In Sub Main ... Keep as much stuff out of the frmDBLoc form so it is not dependent on any type of code that belongs to other modules or classes. This extends its portability.
So it would look something like this.All excellent questions and concerns thanks.Code:Dim X As clsFlorist
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim iColCount as long
Dim i as long
'-----------------
' this code goes in Sub Main
'-----------------
TryAgain:
strDBPath = frmDBLoc.PathFilename
If Len(strDBPath) = 0 Then
strDBPath = PathSlash(App.Path) & "xxx.MDB"
End If
If Not FileExists(strDBPath) Then
frmDBLoc.Setup vbModal, strDBPath
if frmDBLoc.Cancel then
End ' This is only for simplicity and should
' be done better
End if
Goto TryAgain
End If
'-----------------
iColCount = bookcollection.count
For i = iColCount to 1 step -1
bookcollection.remove i
next i
cn.Open "Provider=MSDASQL.1;Persist Security Info=False;
Extended Properties=DBQ=" & PathSlash(strDBPath) & "Wings DB.mdb;
DefaultDir=" & PathSlash(strDBPath) & ";
Driver={Microsoft Access Driver (*.mdb)};
DriverId=281;FIL=MS Access;
FILEDSN=" & PathSlash(strDBPath) & "DSN\WingsDSN.dsn;MaxBufferSize=2048;
MaxScanRows=8;
PageTimeout=5;SafeTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;"
rs.ActiveConnection = cn
rs.Source = "select * from [wings orders]"
rs.Open
Just a performance tid-bit ... for this piece of code, change this code...
This is faster because when you remove the 1st item in a collection, the colletion must reorder all the items below it, and if you have a lot of items, it could really hinder the updating. This new way just takes the last item and removes it and works its way backwards. The collection doesn't have to do any reordering.Code:CHANGE
For i% = 1 To bookcollection.count
bookcollection.Remove 1
Next i%
TO
Dim iColCount as long ' Long is the CPU's default data
' type and is very fast, plus it
' accomodates for large numbers
' when giving up only 2 bytes.
Dim i as long
iColCount = bookcollection.count
For i = iColCount to 1 step -1
bookcollection.remove i
next i
By the way, be careful using S:\ as the drive locations, because if another machine mapped S:\ to a different place, the it won't find the database. Try and use UNC's as much as possible (there are places when not to use them though). For example "\\Server1\Public\Apps\Wings Florist Lookup\Wings DB.mdb"
BUT because you are asking for the location, you need to warn the user of this issue, and to try and put in a comlpete UNC path. It may not matter either because you are asking them. It's all up to you. (It's good to at least know some of the caveat's)Code:cn.Open "Provider=MSDASQL.1;Persist Security
Info=False;Extended Properties=DBQ=S:\WINGS FLORIST
LOOKUP\Wings DB.mdb;DefaultDir=S:\WINGS FLORIST
LOOKUP;Driver={Microsoft Access Driver (*.mdb)};
DriverId=281;FIL=MS Access;FILEDSN=S:\WINGS FLORIST
LOOKUP\Florist Search DSN\WingsDSN.dsn;MaxBufferSize=2048;
MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;"
[Edited by Jaguar on 03-30-2000 at 01:31 PM]
Wait but that doesnt resolve the issue though. Because if i specify the location of the database then it again becomes static. I wanted to stay away from the cn object. In your customized code you still had the cn object in there which would either point to the S: drive or UNC location. But that makes the location static. Maybe this might help. I want to be able to work on this application from home as well without having to keep changing the path of the database & thus get rid of the cn object or make it more dynamic. I hope im not being too confusing. Isnt there any way that i could point the recordset to a connection that was more dynamic as opposed to the static cn i have now.
Thanks for all your suggestions so far, i've learned a lot from them.
i was wondering if i can do this:
eliminate the cn object & then
rs.activeconnection = strDBPath
because strDBPath should contain the name of the path where the database was found.
Would this work??
Yes, I am sorry (again) in my haste I forgot insert the path grabbed from the user into the CN.
You still need to use a CN, but making it as dynamic as possible is the correct end result.
I edited my previous work so that you can see how I think it should be. Everything should be alright now. Any errors, please let me know.
We solved this problem by storing the path to the .mdb in a key in the registry.
If the RegQuery for your key returns an empty string or failed we asked for it's location using similar code to the following
sLocation = vQueryValue("SOFTWARE\your company name\your app name\DSN\", "Location")
If sLocation = "" Then
sLocation = InputBox("MDB Path and File Name : ", "Please .MDB Location", "")
rgCreateNewKey "SOFTWARE\your company name\your app name\DSN\", HKEY_LOCAL_MACHINE
rgSetKeyValue "SOFTWARE\your company name\your app name\DSN\", "Location", sLocation, REG_SZ
End If
That code uses our wrapped registry functions, but you get the idea.
The main point though is that you can use the long forgotten InputBox function (very handy for these things).
.
amcd
Jaguar:
Can i please see how you coded the FileExists()
How does it check whether the file in strDBpath actually exists in the Pathfile(App.Path) folder?