Open a database (1 Viewer)

[mohobrien]

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.

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

 

[ByteMyzer]

Try replacing:

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

...with

Mydb.Execute strSQL

Using the .Execute member of the DAO.Database object allows you to execute an action query without the warning statements, without having to use the SetWarnings command. It also ensures that the action query is executed in the specified Database.

 

[mohobrien]

Thanks ByteMyzer! That does solve the problem with the first DoCmd but now I can't find a way to do the TransferDatabase without using DoCmd.
I tried the maketable query, writing the table to a dbf file but I couldn't get the syntax right. (See the second code snippet here)
http://www.access-programmers.co.uk/forums/showthread.php?t=137040

 

[ByteMyzer]

Try replacing:

DoCmd.TransferDatabase acExport, "dBase 5.0", "C:\Temp\", acQuery, "qryCreateFinalShapeTable", "NS.dbf"

...with:

Mydb.Execute "SELECT * INTO [dBase 5.0;database=C:\Temp].NS FROM NSOutput"

Note that, in the connection string, and in the table name, there are NO single-quotes or double-quotes used. This is why you were having trouble.

 

[mohobrien]

Once again, thank you. I finally decided to print out the help files and right on the front page I put your syntax tips. I'm sure to crash and burn again but it won't be because of quotes, semicolons or square brackets.