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

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

"Matthew Ellis" wrote:

> 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

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

Matthew Ellis wrote:
> 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).

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea