I am making a dbf file with code under a button on a form in arcview with the data for the dbf coming from a query in Access. If I have Access open the routine runs fine. If it is not open, I can loop through the table names and delete a table. Things come crashing to a halt however as soon as I try to run an SQL. The error I get is Error 2075, This operation requires an open database. Any ideas? I'm just a cut and paste man, never seem to have time to actually learn anything.
Code:
Private Sub Make_DBF_Button_Click()
Dim Mydb As DAO.Database
Dim strSQL As String
Dim target_path As String
Dim target_dbf As String
Dim tbl As TableDef
Dim tbldefs As TableDefs
Dim ObjectExists As Boolean
Set Mydb = DBEngine.OpenDatabase("G:\Common\Access_Databases\NewGIS.mdb") ' Hard coded paths need setting
target_path = "C:\temp" ' Hard coded values need setting
target_dbf = "NS" ' Hard coded values need setting .. add dims for variables in SQL
ObjectExists = False
On Error GoTo ErrorHandler ' run away if something goes wrong.
' first query based on parameters
strSQL = "SELECT AllDisp.[Disposition Number], [lsd] & '-' & [section] & '-' & [twp] & '-' & [rge] & 'W' & [m] AS lands INTO tblCreateNewLands " & _
"FROM AllDisp INNER JOIN (Claim_Lands LEFT JOIN ConversionTAbleLSD ON Claim_Lands.Qualifier = ConversionTAbleLSD.Portion) ON AllDisp.id = Claim_Lands.ALLdispConnect " & _
"WHERE (((AllDisp.[Disposition Number])>'S-142330') " & _
"AND ((AllDisp.[Current Status])='ACTIVE') " & _
"AND ((AllDisp.[Mining District])='S'))"
For Each tbl In Mydb.TableDefs ' you can't overwrite an existing table unless ...
If tbl.Name = "tblCreateNewLands" Then
ObjectExists = True
End If
Next tbl
If ObjectExists Then
Mydb.TableDefs.Delete "tblCreateNewLands" ' ... you get rid of it first.
ObjectExists = False
End If
DoCmd.SetWarnings False ' supresses the "Are you sure you know what you're doing?" message
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
If UCase(Dir(target_path & "\" & target_dbf & ".dbf")) = UCase(target_dbf & ".dbf") Then
Kill target_path & "\" & target_dbf & ".dbf" ' same scenario as with the table
End If
' Run the final query and export to dBase 5.0 in one shot.
DoCmd.TransferDatabase acExport, "dBase 5.0", "C:\Temp\", acQuery, "qryCreateFinalShapeTable", "NS.dbf"
Mydb.Close
Set Mydb = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error number " & Err.Number & ": " & Err.Description
Mydb.Close
Set Mydb = Nothing
End Sub