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.


Sponsored Links:



Hello,

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:


	Code:
	   tdfLink = CurrentDB.CreateTableDef("MyTable")
   tdfLink.Connect = ConnectStr
   tdfLink.SourceTableName = "Table1"
   CurrentDb.TableDefs.Append(tdfLink)
   CurrentDB.TableDefs.Refresh

' ... 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?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner




Hi,

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.

Thanks




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:

	Code:
	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
      .Execute
   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.

Thanks!




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:


	Code:
	    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()
Call ComparePARTDATA("PARTDATAMaster", "PARTDATA")
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 MTX, AHORSRV, DHTHRESH, MDRSSTHS, DMINRSSI, CONNECT, IGNORE As String
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"
dbs.Execute ("CREATE TABLE PARTDATACorrections(MTX TEXT, IGNORE TEXT, RSSACMIN TEXT, SS_SUFF TEXT);")

recPARTDATAMaster.MoveFirst
recPARTDATA.MoveFirst

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
recPARTDATA.MoveNext
If recPARTDATA.EOF 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 = ""
AHORSRV = ""
DHTHRESH = ""
MDRSSTHS = ""
DMINRSSI = ""
CONNECT = ""
IGNORE = ""

recPARTDATAMaster.MoveNext
recPARTDATA.MoveFirst

Loop

20 Set recPARTDATAMaster = Nothing
Set recPARTDATA = Nothing
dbs.Close

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:


	Code:
	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.


	Code:
	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
Center')"

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?

Thanks
Tom




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
Thanks




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!
Thanks




Hi guys . I saw and example in code repository


	Code:
	

Export All Database Objects Into Text Files 
by ghudson  

http://www.access-programmers.co.uk/...ad.php?t=66320


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.
HELP!!!!!!!!!
Thanks,
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.
Thanks,
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




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

Sub CREATE_TABLE()
Dim MySQL As String

MySQL = "CREATE TABLE exTable (ID integer, Name text (50))"
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
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.