There has got to be an easier way

[Scoty]

Hi guys,
Well It's almost the beginning of the year and time to swap out those old databases. I have a problem. I am building a replacement Personal Time Off database that will be used in several different location so Problem # 1 is I need to be able to dynamically tell where my database is located. I tried app.path I just got errors so I did this

AppPath = CStr(Left(CurrentDb.Name, Len(CurrentDb.Name) - 12))' 12 happens to be the length of the name of my database

This gave me the directory where the db lives. Now prob #2 I have to extract information out of an old database to save me from having to go location to location to extract the data manually. So I created a initialization form that pulls info from the old database sort and saves it to the new database. Sound pretty easy.(Yep I thought so to). Now I get the dreaded error 13 type mismacth here is what I have:

Dim AppPath As String
AppPath = CStr(Left(CurrentDb.Name, Len(CurrentDb.Name) - 12))
If Dir(AppPath & "ptotime.mdb) = "" Then
MsgBox "PTO 2002.mdb must be located in the same drive as last years PTOTIME.mdb" & Chr(13) & "Either move this database to that location or move that database to " & AppPath, vbCritical + vbOKOnly, Environ("Full")
            Application.Quit
            Exit Sub
        End If

This seems to work ok but when I get to :

        Dim lrsYearEnding As Recordset
        Dim lsYearEndingSQL As String
        Dim dbYearEnding As Database
        Dim lrsCurrentDB As Recordset
        Dim lsCurrentDBSQL As String
        
        Set dbYearEnding = AppPath & "ptotime.mdb"

I get an error type mismatch 13. Debug hilites the amperstand. My question is how do I set the dbYearEnding Reference without using strings? I am not sure what others have installed on their workstations so I have to keep my references to a bare minimuml.

I do appologize for the length of this post. Any replies will be appreciated

Thanks
Scoty :
"Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[gskinner]

I'm not sure what you are trying to do in the first part of the code, however, I'm assuming that the current database is NOT ptotime.mdb, but that it is a code database linked to ptotime. If so, then this should solve your last data type error (13):

[tt]Set dbYearEnding = DBEngine.Workspaces(0).OpenDatabase(AppPath & "ptotime.mdb", False, False)[/tt]

In your code example, you are attempting to assign a string value to a database variable - this will generate an error - though I would have expected an invalid object error. The code above works fine - assuming that the variable AppPath actually does contain the database path string. To check the actual string being passed to insure you have it right you might debug as follows:

[tt]dim strDB as string

strDB = AppPath & "ptotime.mdb"

debug.print strDB
' or, alternatively
msgbox strDB

Set dbYearEnding = DBEngine.Workspaces(0).OpenDatabase(strDB, False, False)[/tt]

Finally, regarding app.path:

app.path returns the path to the current database. If ptotime.mdb is in the same folder as the current database the following should work just fine:

[tt]
strDB = app.path & "\ptotime.mdb" ' Don't forget the "\"
Set dbYearEnding = DBEngine.Workspaces(0).OpenDatabase(strDB, False, False)
[/tt]

If ptotime is not in the same folder as the current database, you can't use it. The best solution then is to check your database connections using DAO or ADOX and if the tables are not available, then do one of the following:

1. Prompt the user to supply the path to the database. I like to present the Windows Common Dialog box and allow the user to navigate to the database and select it.

2. Read the path from an ini file that you store in the current path (app.path). You'll have to use low-level file io calls to do this or use the FileSystemObject.