Execute a Make Table Query in VB code

[DavidRidgway]

I am trying to execute a Make Table Query in a VB program.

I have tried the following:

Set RS = DB.OpenRecordset("Select * from [Query Name")

This gives me error #3417 "An action query cannot be used as a row source"

I also tried the following:

Set MyWorkspace = Workspaces(0)
Set DB = OpenDatabase(stDatabasename, False, False)
MyWorkspace.BeginTrans
mDB.Execute "Select * from [Query Name]"
MyWorkspace.CommitTrans

This gives me error #3065 "Cannot execute a Select query"

Can anyone help me here please?

[Nitro]

Hello David!

If you are connecting to an access database, look at either Command1 or Command2.

If you are connecting to an ODBC database, look at either Command3 or Command4.

Another thing, have you reference to the dll files yet up Project-Reference?

Don't forget to use [] for table names that has two words.

Keep me updated.

Private Sub Command1_Click()
   Dim wrkDefault As Workspace
   Dim dbsTest As Database
   Dim rstEmployees As Recordset

   Position = InStr(1, CurDir, "e\", 0)
   Set wrkDefault = CreateWorkspace("", "", "", dbUseODBC)
   Set dbsTest = OpenDatabase(Left(CurDir, Position) & "\TestFile.mdb")
   Set rstEmployees = dbsTest.OpenRecordset("Inventory_State")

   wrkDefault.BeginTrans
   With rstEmployees
      Do Until .EOF
         .Edit
         !Last_Name = "Swampy"
         .Update
         .MoveNext
      Loop
         
      If MsgBox("Do you want to save?", vbYesNo) = vbYes Then
         'Permanently set the results of the
         'recordset to the database
         wrkDefault.CommitTrans
      Else
         'Discard all the changes you have made
         'for the Do Until Loop
         wrkDefault.Rollback
      End If
      
      .Close
   End With
   
   'Data1 has no connection to ODBC - therefore
   'this is the technique to update the DBGrid
   Set rstEmployees = dbsTest.OpenRecordset("Inventory_State")
   Set Data1.Recordset = rstEmployees
   rstEmployees.Close
   
   dbsTest.Close
End Sub

Private Sub Command2_Click()
   Dim dbsTest As Database
   Dim rstEmployees As Recordset

   Position = InStr(1, CurDir, "e\", 0)
   Set dbsTest = OpenDatabase(Left(CurDir, Position) & "\TestFile.mdb")
   '"SELECT * FROM Employees",
   dbsTest.Execute "Insert Into Inventory_State(First_Name) VALUES ('Swamp Monster')"
End Sub





'*************************** ODBC ***************
Private Sub Command3_Click()
   Dim wrkDefault As Workspace
   Dim dbsTest As Database
   Dim rstEmployees As Recordset
   
   Set wrkDefault = CreateWorkspace("", "", "", dbUseODBC)
   Set dbsTest = wrkDefault.OpenDatabase("PUBS", dbDriverNoPrompt, False, "ODBC;DSN=pubs;DATABASE=pubs;UID=pubs;PWD=pubs;")
   Set rstEmployees = dbsTest.OpenRecordset("Inventory_State", dbOpenDynaset, dbExecDirect , dbOptimistic)
   
   wrkDefault.BeginTrans
   With rstEmployees
      Do Until .EOF
         .Edit
         !Last_Name = "Swampy"
         .Update
         .MoveNext
      Loop
      
      If MsgBox("Do you want to save?", vbYesNo) = vbYes Then
         'Permanently set the results of the
         'recordset to the database
         wrkDefault.CommitTrans
      Else
         'Discard all the changes you have made
         'for the Do Until Loop
         wrkDefault.Rollback
      End If
      
      .Close
   End With
   
   'Data1 has no connection to ODBC - therefore
   'this is the technique to update the DBGrid
   Set rstEmployees = dbsTest.OpenRecordset("Inventory_State", dbOpenDynaset, dbExecDirect , dbOptimistic)
   Set Data1.Recordset = rstEmployees
   rstEmployees.Close
   
   dbsTest.Close
End Sub

Private Sub Command4_Click()
   Dim dbsTest As Database
   
   Set dbsTest = OpenDatabase("PUBS", dbDriverNoPrompt, False, "ODBC;DSN=pubs;DATABASE=pubs;UID=pubs;PWD=pubs;")
   dbsTest.Execute "Insert Into Inventory_State(First_Name,Last_Name,Age) VALUES ('Lonster','AA',23)"
End Sub