Run-time error 3024

Hello Experts,

I'm using the following code in Access 2003 to create a temp table.
Option Compare Database
Option Explicit

Private Sub cmdCreate_Click()

    Call TempTablesSample

End Sub

Sub TempTablesSample()

'
'This subroutine illustrates how to use a temporary MDB in your app.
' If the temporary MDB is present then delete it.
'    The name of the temporary MDB created is the same as the current Front End (FE) name with
'    " temp" added to the end of the name.
' Create the temporary MDB.
' Create the temporary table(s) required in the temporary MDB.
' Link to those tables within your current FE
'   Do whatever you want
' Unlink the tables'
' Delete the temporary MDB

' While this code is copyright 2000 by Tony Toews it's all code strung together from the online help so do
'   whatever you like with this.   At your own risk.



    Dim tdfNew As TableDef , RS As Recordset
    Dim wrkDefault As Workspace
    Dim dbsTemp As Database, strTempDatabase As String
    Dim strTableName As String
    Dim Db As Database
    Dim strOpenPwd As String
  

    ' Get default Workspace.
    Set wrkDefault = DBEngine.Workspaces(0)
    strTempDatabase = left$(CurrentDb.Name, Len(CurrentDb.Name) - 4) & " temp.mdb"

    ' Make sure there isn't already a file with the name of
    ' the new database.
    
    If Dir(strTempDatabase) <> "" Then Kill strTempDatabase

    'Create a new temp database
    Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase, dbLangGeneral)

    Call closeConnection
    DoEvents
    strOpenPwd = ";pwd=" & "MySuperPwd"
    Set obDB = OpenDatabase(Name:=CurrentProject.Path & "temp.mdb", Options:=True, ReadOnly:=False, Connect:=strOpenPwd)
    DBPass = "MySuperPwd"
    Call closeConnection
    DoEvents
    Call OpenConnection
  

    strTableName = "temp"         'strBracketedTableName = "[" & strTableName & "]"
    
    

   
    ' Delete the link to the temp table if it exists
    If TableExists(strTableName) Then
       CurrentDb.TableDefs.Delete strTableName
    End If
    
    ' Create the temp table
    Set tdfNew = dbsTemp.CreateTableDef(strTableName)
    With tdfNew
        .Fields.Append .CreateField("field1", dbLong)
        .Fields.Append .CreateField("field2", dbInteger)
        .Fields.Append .CreateField("field3", dbInteger)
        .Fields.Append .CreateField("field4", dbInteger)
        .Fields.Append .CreateField("field5", dbInteger)
        dbsTemp.TableDefs.Append tdfNew
    End With
    


    dbsTemp.TableDefs.Refresh
    Dim tdfLinked As TableDef

    ' Link to the Import tables in the temp MDB
    Set tdfLinked = CurrentDb.CreateTableDef(strTableName)
    tdfLinked.Connect = ";DATABASE=" & strTempDatabase
    tdfLinked.SourceTableName = strTableName
    CurrentDb.TableDefs.Append tdfLinked
    
    CurrentDb.TableDefs.Refresh
    
    RefreshDatabaseWindow

    Set RS = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset, dbAppendOnly)


' Do your logic to the temp tables here.
    


    RS.Close
    dbsTemp.Close

    CurrentDb.TableDefs.Refresh

    Set RS = Nothing
    Set dbsTemp = Nothing

    ' Unlink the tables
    CurrentDb.TableDefs.Delete strTableName

    ' Delete the temp mdb
strTempDatabase = left$(CurrentDb.Name, Len(CurrentDb.Name) - 4) & " temp.mdb"
    Kill (strTempDatabase)



tagExit:
        
    Exit Sub
    
tagError:
    DoCmd.Hourglass False
    If Err.Number = 70 Then
        MsgBox "Unable to delete temporary database as it is locked." & vbCrLf & vbCrLf & _
            "Import cancelled."
    Else
        MsgBox Err.Description, vbCritical
    End If
    
End Sub

Function TableExists(strTableName As String) As Integer
    ' Checks for the existance of a specific table

    'Added the tabledefs.refresh as tables just added in this session weren't
    '   being picked up.

    Dim dbDatabase As Database, tdefTable As TableDef

    On Error Resume Next
    Set dbDatabase = DBEngine(0)(0)
    dbDatabase.TableDefs.Refresh
    Set tdefTable = dbDatabase(strTableName)
    
    ' If an error occurred the tabledef object could not be accessed and
    ' therefore doesn't exist. This could cause problems in a secured environment
    ' though as access may be denied.
    If Err = 0 Then
        TableExists = True
    Else
        TableExists = False
    End If

End Function

Open in new window


That part works fine. I'm also trying to hard-code a password for the temp .mdb (lines 49-56) and using this code in a standard module  
Option Compare Database

Public obDAO            As DAO.Workspace
Public obDB             As DAO.Database
Public DBPass           As String
Public ConnectionState  As Boolean

Sub OpenConnection()
On Error GoTo ErrTrap
    Set obDAO = DAO.DBEngine.Workspaces(0)
    Set obDB = obDAO.OpenDatabase(strDBPath, False, False, ";pwd=" & DBPass & "")
    ConnectionState = True
    Exit Sub
    
    
ErrTrap:

    If Err.Number = 3031 Then
        MsgBox "The password you have entered is not valid for this database.", vbExclamation + vbOKOnly, "Invalid Password"
        Exit Sub
    End If
    
End Sub

Sub closeConnection()
   ' obDB.Close
    ConnectionState = False
'    Set obDB = Nothing
End Sub

Open in new window

but I get a 3024 run-time error on line 52.

Any help solving this problem will be greatly appreciated.

p.s.
Attached please find an Example mdb
Example.mdb
LVL 8
hello_everybodyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
3024 generally means it cannot find the file. Are you 100% certain that the file is at the location you specified, and that you've spelled everything correctly?
0
hello_everybodyAuthor Commented:
Yes, I'm sure, the file is there, and I think everything's spelled correctly.  
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You should check again. VBA Error 3024 is "Cannot Find File". The cause of this is either (a) misspelling or (b) wrong syntax or (c) the file is not in the intended location.

Are you sure that CurrentProject.Path returns the fully formed path, with the ending backslash? If not, then you'll have to add it:

CurrentProject.Path & "\Temp.mdb"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hello_everybodyAuthor Commented:
Thanks, I figured it out. I changed Name:=CurrentProject.Path & "temp.mdb" to Name:=strTempDatabase and now it works!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hello_everybodyAuthor Commented:
On second thought, award LSMConsulting's comment #35367848 as the assisted solution with the full amount of points. After all it was his prodding that led me in the right direction.
0
hello_everybodyAuthor Commented:
Based upon LSMConsulting's answer which I had known before, I made a change a my approach and thus solved this problem, and another one which stemmed off of this one which I was going to post after this one was solved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.