Execute a Make Table Query in VB code

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?

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.

Code:

---

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

---