Connecting to Access DB using info from Windows Registery

[arif]

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??

[Jaguar]

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 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

This is the complete set of code that goes in the frmDBLoc form. It's quite simple and does exactly what I want.

It incorporates a degree of OOP, but could be enhanced quite a bit.

Enjoy!!

[arif]

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.

[arif]

sorry forgot to change in the above code....

rs.ActiveConnection = cn NOT strDBPath

I was trying to test that out

[Jaguar]

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.

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

All excellent questions and concerns thanks.


Just a performance tid-bit ... for this piece of code, change this 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

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.

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"

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;"

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)

[Edited by Jaguar on 03-30-2000 at 01:31 PM]

[arif]

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.

[arif]

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??

[Jaguar]

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.

[amcd]

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

[arif]

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?