Create table dynamically

I currently have form that dynamically creates a SQL string (based on user selections) that filters a report. My users may select hundreds of records for this filter so there is a need to save the filter.

So, my solution was to create a make table query. Is there a way to prompt the user to either create a new table for the query to run against or select an existing table? This has to be per time so coding a table into the Make Table query would not work.

Any advice or assistance would be much appreciated.

Post your answer or comment

comments powered by Disqus

I am trying to dynamically creating tables in Access 2010.
I would like to get the info from the user (Table name, number&type of columns)
and then creating the table using an SQL Query.

I know how to write the query, but I need help with passing the arguments to the query.

Please help me out, Thanks

I'm trying to create a table in an external database, that is based upon information found in a LINKED table from another external database. However, there are a LOT of fields, and I'd rather not have to specifically declare and type each field in a create table statement.

So, I thought, that i could Link the table temporarily and design a query off of it, that would include the specific fields I wish to preserve from the linked table, and then use that query as the source for a Select Into statement. Thus I do this as follows:

	   tdfLink = CurrentDB.CreateTableDef("MyTable")
   tdfLink.Connect = ConnectStr
   tdfLink.SourceTableName = "Table1"

' ... a bit later in another command button executed code

   set dbTmp = CreateDatabase("NewDb.mdb", dbLangGeneral)
   DoCmd.RunSQL "SELECT * INTO tbl_NewTable IN NewDb.mdb FROM qry_MyQuery;"

Now qry_MyQuery is designed off of the tabledef "MyTable" thus, when the program is dormant, there is no linked table and qry_MyQuery is inaccessible. (causes errors about the missing source table)
However, at Run-Time, the source table is present, and the query will display in a datasheet view subform that i have on my main form, but in the select into statement it creates an error.
Do I have to do EVERYTHING by hand with this, since the linked table is dynamically linked at run time?

Jaeden "Sifo Dyas" al'Raec Ruiner


I am trying to create a dynamic page in MS Access. But I not sure whether this is possible in Access.

I need to have a button next to each of the query results. For example during first query if there are 5 results, i need to have five buttons next to the query. If the query brings up 10 results, I need to have 10 buttons.
Is this possible in MS Access?

The need for the button is : My table has 64 fields in it. So if I query I just need to bring some four most common fields. A button will be next to each query and by clicking it, I will have to see all of the 64 fields.

Thank you!

I'm trying to create a dynamic form, but I'm not being sucessfull. I created a query call frmReportCard that is using two tables one is a listing of Clients and is link to the Reportcard table by comanyname. When I open the form the option button are not accessablle can anyone please help me and tell me what Am I doing wrong. I have attache a sample of the database that I'm trying to do.

Hi again,

Does Access have a built in method for creating tables in a report? The only option I see is to use the Line tool and manually draw a table.


I have a create table query I execute from VBA code that includes a BIT column. All the columns work perfectly normally except the bit. No matter what I do to attempt to update the value in the new table it stays -1. When I open the table in design view and change the format to Yes/No then it works. Is there some way to set the format in a create table statement?

I execute the first step of creating the table via DAO:

	With qdf
        'create columndefinitions table
      .sql = "CREATE TABLE " & strTableName & "_ColumnDefinitions (tname text(30) not null, fldpos number not null, ctlpage
number null, ctlpos number not null, ctlname text(30) not null, ctldesc text(255), fldtype text(50) null, ctlshow text(5) not
null, ctllookup text(50) null, ctltype number null, fldsize number null, ctlcaption text(255) null, accept bit)"
      .ReturnsRecords = False
   End With

The table is created but the value of the accept for each and every record is -1. Later I attempt to update the value to 1 or 0 and it doesn't update until I go into the Design view and set the Format property on the column. I cannot do that as I need to be able to do the creation and updating via code only. I am not doing this manually ever.

I am using Access 2002.


I am looking to create a table via VBA (need to create the table in many different backend databases). I am using the following code, which almost does what I need it to do:

	    db.Execute "CREATE TABLE tblSessions (SessionID Counter CONSTRAINT MyFieldConstraint PRIMARY KEY, YearID long, CampusID
long, StartDate date, EndDate date, CreatedBy long, DateCreated date, ModifiedBy long, DateModified date);"

The problem is that because of the way this database works, I have many different field users working off independent copies and syncing with a backend. The normal incrementing primary key created lots of issues for me, so I switched it to Random and have been good to go since. I know there is still some room for duplication, and if there is a better solution to avoid dup keys in independent copies I would be happy to hear it.

But back to the issue at hand, Is there any way to create the table through SQL (similar to what is above) with a Random primary key instead of using Increment or Counter. I have tried playing with the syntax myself to no avail and my searches for an answer have come up empty.

If you cannot do it through pure SQL, I would have to believe there is a way in VBA to change the New Values property of the primary key to Random after it is created. However, I have no idea how to go about this.

Any help or push in the right direction would be greatly appreciated. Thanks!

Well I post my code so it can be looked at but my problem seems to stem from this statement:

dbs.Execute ("CREATE TABLE PARTDATACorrections(MTX TEXT, IGNORE TEXT, ....);")

VBA or Access 97 or whatever the reason won't let me use IGNORE as a Field Name. Is this a reserved word? If so, then why or how do I work my way around it, because I need it to create a table with the one of the fields labeled IGNORE.

Sub testComparePARTDATA()
End Sub

Sub ComparePARTDATA(PARTDATAMaster As String, PARTDATA As String)

Dim dbs As Database
Dim recPARTDATAMaster As Recordset
Dim recPARTDATA As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim FieldChanged As Boolean
Dim Check2, Check3, Check4, Check5, Check6, Check7 As Boolean
Dim m As Integer

Set dbs = CurrentDb

Set recPARTDATAMaster = dbs.OpenRecordset(PARTDATAMaster)
Set recPARTDATA = dbs.OpenRecordset(PARTDATA)
Set tdf = dbs.TableDefs(PARTDATAMaster)

dbs.TableDefs.Delete "PARTDATACorrections"


Do Until recPARTDATAMaster.EOF

m = 0
10 For Each fld In tdf.Fields
If m = 0 Then MTX = recPARTDATAMaster(fld.Name)

If recPARTDATAMaster(fld.Name) <> recPARTDATA(fld.Name) Then
If m = 0 Then
MsgBox "Missing MTX" & MTX
GoTo 20
End If
GoTo 10
ElseIf m = 2 Then
Check2 = True
AHORSRV = recPARTDATAMaster(fld.Name)
ElseIf m = 3 Then
Check3 = True
DHTHRESH = recPARTDATAMaster(fld.Name)
ElseIf m = 4 Then
Check4 = True
MDRSSTHS = recPARTDATAMaster(fld.Name)
ElseIf m = 5 Then
Check5 = True
DMINRSSI = recPARTDATAMaster(fld.Name)
ElseIf m = 6 Then
Check6 = True
CONNECT = recPARTDATAMaster(fld.Name)
ElseIf m = 7 Then
Check7 = True
IGNORE = recPARTDATAMaster(fld.Name)
End If
FieldChanged = True
End If

m = m + 1
Next fld

If FieldChanged = True Then
dbs.Execute ("INSERT INTO PARTDATACorrections(MTX, AHORSRV, DHTHRESH, MDRSSTHS, DMINRSSI, CONNECT, IGNORE) " & "VALUES('" & MTX & "', '" & AHORSRV & "', '" & DHTHRESH & "', '" & MDRSSTHS & "', '" & DMINRSSI & "', '" & CONNECT & "', '" & IGNORE & "');")
End If

FieldChanged = False
MTX = ""



20 Set recPARTDATAMaster = Nothing
Set recPARTDATA = Nothing

I know this code may seem a little confusing but it all works except for the line where I try to create the table PARTDATACorrections. When I run the code it gives me a Syntax error. I appreciate any help on this. Thanks

I have a need to create tables using vba (DAO)

I have two tables, tblCreateTable & tblCreateTableFields

After I have selected the table I need to create in tblCreateTable, I need to create a recordest of the table's fields in tblCreateTableFields and create the table.

I understand how to select the table, and recreate the recordset

I am unsure of how to:
* Create the table
* loop through the recordset and add the fields to the table

Thanks in advance

I am usually able to figure this stuff out on my own nowadays, but I am really stuck, so I defer to your knowledge.

I am trying to use .Execute to run SQL to create a table in the database, the problem is that the table name is a string value that was determined through code.

If I don't use a string for the table name, I don't get an error, but with a string I am getting a syntax error that I can't seem to fix. Here is an example:

	StrTablename = "Autopay Accounts"
Currentdb.Execute "CREATE TABLE " & StrTablename & " (RecordStatus varchar(2))"

Anyone have an idea? I think it's a problem with quote's somewhere.

Hi Everyone,

I'm trying to create a table using DoCmd.RunSQL. The SQL would join two tables and create a third table.

The SQL works when I do the join using just the select statement. But I want it to be a permanent table as I will use it for a whole bunch of analysis.

Can anyone see what I'm doing wrong here? The error says it's something wrong with my SQL syntax.

	Sub JoinTables()

Dim sqlJoin As String

sqlJoin = "CREATE TABLE ATAPHR AS (SELECT allhr.PersonID, allhr.OrgType, ATAPRequests.* FROM ATAPRequests LEFT JOIN allhr ON
ATAPRequests.[Requestor PIN] = allhr.PersonID WHERE ATAPRequests.SSG = 'Personal Market' AND allhr.OrgType = 'Corporate

DoCmd.RunSQL (sqlJoin)

DoCmd.OpenTable "ATAPHR"

End Sub

I am creating a table in my back_end db with this private statement, but I get an error.

Quote: strSQL = "CREATE TABLE ReturnPractitionerSub ([ReturnNo] LONG idxReturnNo KEY ,[VisitNo] COUNTER PRIMARY KEY,[Time] DATETIME,[Cost] MONEY, CONSTRAINT PractitionerReport FOREIGN KEY (ReturnNo) REFERENCES Return (ReturnNo));" The problem seems to be the 'idxReturnNo KEY' because this code works...

Quote: strSQL = "CREATE TABLE ReturnPractitionerSub ([ReturnNo] LONG UNIQUE,[VisitNo] COUNTER PRIMARY KEY,[Time] DATETIME,[Cost] MONEY, CONSTRAINT PractitionerReport FOREIGN KEY (ReturnNo) REFERENCES Return (ReturnNo));" My problem is, I want two KEY fields. ReturnNo and VisitNo.

Can I do this?


I am running a docmd.runsql CREATE TABLE...... which works fine when I define text, number or date data types but refuses to recognise types like autonumber?
Any ideas on why or a workaround

Can a CREATE TABLE sql create a table in a different file? More specifically I want to use it to Create a table in the backend and if yes how could one link to it?
I know the table could be created, copied to a different db and then deleted but I was hoping there's syntax in the CREATE TABLE to do all this for me!

Hi guys . I saw and example in code repository


Export All Database Objects Into Text Files 
by ghudson

and it was writing all the access object to text file. I want to do similer but instead of adding just feild parts with its population . I want the create table statment for each table in access database. I want the create table satemetns be corect so if i paste it in sql view of access db it runs and creates that table for me. I want the table name , feild name,type,size and primary key and forgin key as well.I be happy if some expert show me how to do this.Thanks

Can someone please help with this, it6 is having problem creating number field with 2 Decimal places

DoCmd.RunSQL "CREATE TABLE Temp (Speed_Band Text, Site_ID INTEGER, In decimal(10,2), Out decimal(10,2));"

I am trying to run a VBA code that deletes a table and then creates a new one using DoCmd.RunSQL. Does anyone have any suggestions for syntax using the CREATE TABLE. I keep getting error messages.
Terri Hodkins

I finally figured out how to delete a table and then create a new one. I am now stuck with not being able to add any more columns. My formula doesn't want to allow me to add any fields with the value as DATETIME.
I would greatly appreciate any suggestions. As you can tell I am very new to programming.
Terri Hodkins

On Error GoTo cmdDetailActual_Err

Dim strSQL As String

DoCmd.Echo True, ""
DoCmd.DeleteObject acTable, "tblDetailActual"

strSQL = "CREATE TABLE tblDetailActual" & "(LotNumber TEXT(10),Commodity TEXT(100),RanchPlot TEXT(100),Invoice LONG,TotalAmount CURRENCY)"
DoCmd.RunSQL strSQL

Exit Sub

I want to create a table from VBA code like this example:

Dim MySQL As String

MySQL = "CREATE TABLE exTable (ID integer, Name text (50))"
DoCmd.SetWarnings False
DoCmd.SetWarnings True
End Sub

My problem is i don't know which datatype i should use to create a memo field or how to create an autonumber field.

How many way to create a table automatically through VBA?

Thanks for you answers and please excuse my english.

I've created the coding required to create a new table, but I also need to set the 'Required' parameter to 'Yes' on some selected fields.

Is is possible to do this whilst creating the table with the 'CREATE TABLE...' statement or is there another way of programatically setting the 'Required' parameter?

Brothers... Can anyone please tell me how I should create tables on MS SQL Database complying with the attached XSD. I have tried different ways but it doesnt seem to conform with the XSD. I think its something to do with how I set tables and its related fields.


        MHEG EPG/PVR input data format. version 0_6
        Version 0_6
          - Removed unreferenced RecommendationsType type and recommendations element.
          - Removed unneccessary escape chars from date checking regex. 
        Version 0_5
            - A single mandatory Channel element per document.
            - Removed Listings level DefaultAuthority.
        Version 0_4
            - Synopsis is now optional for programmes, series and recommendations.
        Version 0_3
            - Made programme level CRID optonal to aid integration with exitsing (non-PVR) data sources.
               NOTE:  The programme level CRID must be defined when PVR functionality is required.
        Version 0_2
            - Added RecommendationsType to ProgrammeType
            - Added RecommendationType to RecommendationsType
            - Added SeriesType to ProgrammeType to allow a single series  to be defined per programme
            - RecommendationType and SeriesType define title, synopsis and single crid
        Version 0_1
              - Initial draft based on SimpleEPG.dtd with TVAnytime extentions.
                The root of the XML document
                CRID element is optional only to aid integrattion with existing non-PVR data sources.
                CRID is mandatory at the programme level when for PVR functionality to work.
                The TVAnytime CRID.  Example:  crid://
                May be shortenned to /01DF5#one is default_authority covers this entry.
                The Default Authority in scope of the location of the default_authority.  Example default_authority: 
                characters in this field define the arbitrarality named graphic file also delivered with the data.   
                Example:  classification="15" describes a file named 15.png that is to be displayed by the EPG.
                The name of the channel as seen on TV.
                Unix type identifier for the channel
                The  title of the television programme.  The length must be defined by the design of the EPG so cannot be
restricted here.
                The  synopsis for the television programme.  The length must be defined by the design of the EPG so cannot be
restricted here.
                Format = dd/MM/yyyy HH:mm:ss
                Once channel of programme data per XML document is recommended, though no restriction is made here.
                The list of programme schedule data must be contiguous in scope of the current EPG.


I have an Access ADP project connected to an SQL Server 2005 DB.
In VBA, I want to export a recordset given from a previous query to Excel
using TransferSpreadsheet. As this command can only accept a table as its
parameter, I first create a new table -to use as a temporary one-, then I
insert the specific rows in it, export it and, finally, delete it from the DB.
The process is triggered through a command button.
However, this doesn't work. I get the message that it can't find the table
'tmptbl_export'. The reason for this is a delay in creating the table. If I
press the button while I have the 'All Access objects' pane open, I can see
that I get the message and -after I press OK- then the table is created!

I give the code below:

Private Sub cmd_export_to_excel_Click()

Dim sql_str As String
Dim rst As Recordset
Dim tableExists As Boolean

Dim errNum As Integer
Dim fileName As String
Dim filePath As String
Dim fso

Dim fileNotExists As Boolean
Dim fileNumber As Integer
Dim tmpFilePath As String

DoCmd.RunSQL "IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[tmptbl_export]') AND type in (N'U')) " & _
"DROP TABLE [dbo].[tmptbl_export]"

sql_str = "CREATE TABLE [dbo].[tmptbl_export] (" & _
"[fld_company_name] [nvarchar](50) NULL, " & _
"[fld_person_name] [varchar](50) NULL, " & _
"[fld_person_surname] [varchar](50) NULL)"

DoCmd.RunSQL sql_str

sql_str = "INSERT INTO dbo.[tmptbl_export] (fld_company_name, fld_person_name,
fld_person_surname) SELECT dbo.tbl_Companies.fld_company_name, dbo.
tbl_Persons.fld_person_name, dbo.tbl_Persons.fld_person_surname " &

'sql_str = "SELECT dbo.tbl_Companies.fld_company_name, dbo.tbl_Persons.
fld_person_name, dbo.tbl_Persons.fld_person_surname " & _
"INTO dbo.[tmptbl_export] " & txt_sql_conditions.Value

DoCmd.RunSQL sql_str

fileName = CStr(Year(Now)) & (IIf(Len(CStr(Month(Now))) = 1, "0" & CStr(Month
(Now)), (CStr(Month(Now))))) & (IIf(Len(CStr(Day(Now))) = 1, "0" & CStr(Day
(Now)), (CStr(Day(Now)))))
filePath = "P:test_folder" & fileName & "_eortazontes"

Set fso = CreateObject("Scripting.FileSystemObject")

fileNumber = 0

fileNumber = fileNumber + 1
tmpFilePath = filePath & "_" & fileNumber & ".xls"

If Not fso.FileExists(tmpFilePath) Then
fileNotExists = True
End If
Loop While fileNotExists = False

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "dbo.
tmptbl_export", tmpFilePath, True

DoCmd.RunSQL "DROP TABLE tmptbl_export"
End Sub

If I insert a breakpoint right after the insertion of the rows the table gets created. If then I press F5 it runs fine and I get the file with the expected results. That's how I figured it's a matter of this delay and not a matter of the statement itself.

I have also tried doing the creation and insertion in the table with SELECT
INTO (the commented line) but it didn't work either. I also tried to do this
same process with docmd.outputTo but I don't get the expected result, as
docmd.outputTo doesn't support Unicode encoding and I -mostly- have Unicode
in my results.

Does anyone know of any way to overcome this?

Which of these methods do you think is best and why?

-A delete query of the datas of TableX(Back-end).
-An insert query of QueryX to TableX.
-An insert query of QueryY to TableX.

-An union query(QueryZ) of QueryX & QueryY.
-A create table query of the TableX(Front-End) based on QueryZ.

Queries X & Y are long to execute and TableX doesn't need to be live.
I already have a script that does some tasks on that database at night.
I only have to add one task to it.
Should it be #1 or #2?

If you got any other idea, please let me know.

I'm using a series of SQL commands (being executed from a legacy ASP script) to create some tables in an access 2003 database.

I have got as far as creating my tables and constraints using 'CREATE TABLE' but due to the lack of documentation on msdn, i'm struggling to configure some of the more specific field options.

Firstly, I'd like to be able to set a 'Default Value' for a Date field.

Secondly, When creating boolean (BIT) fields in Access itself, there is an option to set the 'Format' for the field, whereby you can set it to True/False.
I'd like to be able to set this from my CREATE TABLE statement rather than having the standard -1/0 values.

I've searched high and low and haven't been able to find any documentation for either of these issues.

Any help/guidance would be much appreciated.

Not finding an answer? Try a Google search.