Create new table in backend through VBA

[Guest]

I am probably missing something simple here, but I am trying to create a new
table based on the name a user inputs in a text box on a form. That part is
fine, but I can't figure out how to create the new table in the backend (a
different .mdb where I store all of the tables). Here is the code I am using:

Private Sub Command2_Click()

Dim newTableName As Variant
Dim db As DAO.Database
Dim strSql As String

newTableName = [Text0]

' SQL string to create a new table
strSql = "SELECT *"
strSql = strSql & "INTO [" & newTableName & "] "
strSql = strSql & "FROM [tblAdjustment];"

'On Error Resume Next ' Delete table if it exists
'DoCmd.DeleteObject A_TABLE, newTableName

' Open pointer to current database"
Set db = CurrentDb()
db.Execute (strSql) ' Execute (run) the query
MsgBox "Table: [" & newTableName & "] created"
db.Close


Dim newerTableName As Variant
Dim strSqll As String

newerTableName = [Text0] & " " & "Oil"

' SQL string to create a new table

strSqll = "SELECT *"
strSqll = strSqll & "INTO [" & newerTableName & "] "
strSqll = strSqll & "FROM [tblOil];"

'On Error Resume Next ' Delete table if it exists
'DoCmd.DeleteObject A_TABLE, newerTableName

' Open pointer to current database
Set db = CurrentDb()
db.Execute (strSqll) ' Execute (run) the query
MsgBox "Table: [" & newerTableName & "] created"
db.Close
DoCmd.Close


End Sub

Thank you for the help.

Matthew Ellis

 

[Guest]

I got it figured out. In case anyone else needs this answer, I replaced Set
db = CurrentDB() with
Set db = OpenDatabase("C:\Path to database\database.mdb")

Thanks,

Matthew Ellis

 

[Bas Cost Budde]

I wonder: what place in your design do these user created tables have?