Create Table VBA help

[Oblio]

I would like to create a table named t_MCRs, utilizing 5 fields from a query, q_MCRs_Only.
Then I would like to also add some new fields to the newly created table immediately after that.
It is preferred that no warnings or popup messages occur. My understanding is I must use the .Execute method to accomplish this but I am having no luck and am getting it to work any way. Here is what I have currently, and it is telling me in debugging that t_MCRs is not defined...

Option Compare Database
Option Explicit

Public Sub createMCRsTable()
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim strtMCRs As String
Dim MCRQuery As String

strtMCRs = t_MCRs

If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & strtMCRs & "'")) Then

DoCmd.SetWarnings False
DoCmd.Close acTable, strtMCRs, acSaveYes
DoCmd.DeleteObject acTable = acDefault, strTableName
Debug.Print "Table" & strtMCRs & "deleted..."
DoCmd.SetWarnings True

End If

On Error Resume Next

MCRQuery = "SELECT q_MCRs_Only.t_Files_ID, q_MCRs_Only.LastFolderIs, q_MCRs_Only.FName, q_MCRs_Only.Full_MCR_Pathway, q_MCRs_Only.[MCR_Data_Entry_Complete?] INTO t_MCRs;"

End Sub



I am stuck at work til I get this figured out so if anybody is bored I could use a hand or three !!!!!

Thanks,

Bill

[OBP]

Where do you specify what t_MCRs type is and it's value?
You introduce at
strtMCRs = t_MCRs
and artrMCRS is specified.
You can use a Make Table Query to create the table or you can do it using VBA and the Table Collections and Definitions like this


Dim data As String, count As Integer, count2 As Integer, Start As Integer, finish As Integer, rstable As Object
Dim db As Database, tbl As TableDef, fld As Field, idx As Index, fname As String, start2 As Integer
Set rstable = CurrentDb.OpenRecordset("Post_Approval_Accepted")
rstable.MoveFirst
'Start by opening the database
Set db = CurrentDb()
'Create a tabledef object
Set tbl = db.CreateTableDef("tblone")
'Create a field; set its properties; add it to the tabledef
Set fld = tbl.CreateField("oneID", dbLong)
fld.OrdinalPosition = 1
fld.Attributes = dbAutoIncrField
tbl.Fields.Append fld
count2 = 1
data = rstable.Field1
Start = 1
For count = 1 To (Len(data) / 2)
If Mid(data, count, 1) = "|" Then
fname = Mid(data, Start, (count - Start))
Start = count + 1
count2 = count2 + 1
Set fld = tbl.CreateField(fname, dbText)
fld.OrdinalPosition = count2
fld.Size = 50
fld.AllowZeroLength = False
tbl.Fields.Append fld
End If
Next count
'Finally add table to the database
db.TableDefs.Append tbl
'Indicate creation was successful
MsgBox "The " & tbl.Name & " table was successfully created"
Set tbl = db.CreateTableDef("tbltwo")
'Create a field; set its properties; add it to the tabledef
Set fld = tbl.CreateField("LoanNo", dbText)
fld.OrdinalPosition = 1
fld.Size = 50
tbl.Fields.Append fld
count2 = 1
start2 = Start
For count = start2 To Len(data) - 1
If Mid(data, count, 1) = "|" Then
fname = Mid(data, Start, (count - Start))
Start = count + 1
count2 = count2 + 1
Set fld = tbl.CreateField(fname, dbText)
fld.OrdinalPosition = count2
fld.Size = 50
fld.AllowZeroLength = False
tbl.Fields.Append fld
End If
Next count
'add Last Field
fname = Right(data, ((count + 1) - Start))
Set fld = tbl.CreateField(fname, dbText)
fld.OrdinalPosition = count2 + 1
fld.Size = 50
fld.AllowZeroLength = False
tbl.Fields.Append fld
'Finally add table to the database
db.TableDefs.Append tbl
'And refresh the database window
MsgBox "The " & tbl.Name & " table was successfully created"
rstable.Close
Set rstable = Nothing
RefreshDatabaseWindow

[Oblio]

Where do you specify what t_MCRs type is and it's value?
You introduce at
strtMCRs = t_MCRs
and artrMCRS is specified.
You can use a Make Table Query to create the table or you can do it using VBA and the Table Collections and Definitions like this


Dim data As String, count As Integer, count2 As Integer, Start As Integer, finish As Integer, rstable As Object
Dim db As Database, tbl As TableDef, fld As Field, idx As Index, fname As String, start2 As Integer
Set rstable = CurrentDb.OpenRecordset("Post_Approval_Accepted")
rstable.MoveFirst
'Start by opening the database
Set db = CurrentDb()
'Create a tabledef object
Set tbl = db.CreateTableDef("tblone")
'Create a field; set its properties; add it to the tabledef
Set fld = tbl.CreateField("oneID", dbLong)
fld.OrdinalPosition = 1
fld.Attributes = dbAutoIncrField
tbl.Fields.Append fld
count2 = 1
data = rstable.Field1
Start = 1
For count = 1 To (Len(data) / 2)
If Mid(data, count, 1) = "|" Then
fname = Mid(data, Start, (count - Start))
Start = count + 1
count2 = count2 + 1
Set fld = tbl.CreateField(fname, dbText)
fld.OrdinalPosition = count2
fld.Size = 50
fld.AllowZeroLength = False
tbl.Fields.Append fld
End If
Next count
'Finally add table to the database
db.TableDefs.Append tbl
'Indicate creation was successful
MsgBox "The " & tbl.Name & " table was successfully created"
Set tbl = db.CreateTableDef("tbltwo")
'Create a field; set its properties; add it to the tabledef
Set fld = tbl.CreateField("LoanNo", dbText)
fld.OrdinalPosition = 1
fld.Size = 50
tbl.Fields.Append fld
count2 = 1
start2 = Start
For count = start2 To Len(data) - 1
If Mid(data, count, 1) = "|" Then
fname = Mid(data, Start, (count - Start))
Start = count + 1
count2 = count2 + 1
Set fld = tbl.CreateField(fname, dbText)
fld.OrdinalPosition = count2
fld.Size = 50
fld.AllowZeroLength = False
tbl.Fields.Append fld
End If
Next count
'add Last Field
fname = Right(data, ((count + 1) - Start))
Set fld = tbl.CreateField(fname, dbText)
fld.OrdinalPosition = count2 + 1
fld.Size = 50
fld.AllowZeroLength = False
tbl.Fields.Append fld
'Finally add table to the database
db.TableDefs.Append tbl
'And refresh the database window
MsgBox "The " & tbl.Name & " table was successfully created"
rstable.Close
Set rstable = Nothing
RefreshDatabaseWindow