473,572 Members | 3,174 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Way to create multiple tables on the fly

Hi guys,

Does anyone know of a way to create multiple tables using information
stored in one table? I have a table with 4 columns (TableName,
ColumnName, DataType, DataSize) and wanted to know if there is a way
to use the information in this table to create the many tables that
are listed in the source table instead of creating each table
individually?

Many thanks for any help you can offer.

Dean...
Jul 14 '08 #1
3 7179
DeanL <de************ *@yahoo.comwrot e in
news:65c1529d-15e1-42a9-86ef-
46**********@m3 6g2000hse.googl egroups.co
m:
Hi guys,

Does anyone know of a way to create multiple tables using
information stored in one table? I have a table with 4 columns
(TableName, ColumnName, DataType, DataSize) and wanted to know if
there is a way to use the information in this table to create the
many tables that are listed in the source table instead of
creating each table individually?

Many thanks for any help you can offer.

Dean...
If the table was generated from Access, I'd just open it in a
DAO.recordset, and loop on the table name, either adding the tables
and their fields to the relevant collection or building a SQL Create
Table atatement.

There may be other ways.
--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **
Jul 15 '08 #2
DeanL wrote:
Hi guys,

Does anyone know of a way to create multiple tables using information
stored in one table? I have a table with 4 columns (TableName,
ColumnName, DataType, DataSize) and wanted to know if there is a way
to use the information in this table to create the many tables that
are listed in the source table instead of creating each table
individually?

Many thanks for any help you can offer.

Dean...
Here's an example from help using "CreateTableDef "

Sub NewTable()
Dim dbs As Database, tdf As TableDef, fld As Field

' Return reference to current database.
Set dbs = CurrentDb
' Return TableDef object variable that points to new table.
Set tdf = dbs.CreateTable Def("Contacts")
' Define new field in table.
Set fld = tdf.CreateField ("ContactNam e", dbText, 40)
' Append Field object to Fields collection of TableDef object.
tdf.Fields.Appe nd fld
tdf.Fields.Refr esh
' Append TableDef object to TableDefs collection of database.

dbs.TableDefs.A ppend tdf
dbs.TableDefs.R efresh
Set dbs = Nothing
End Sub
Also, you should look at the "Create Table" help topic. You can create
the table and fields via SQL
Jul 15 '08 #3
On Mon, 14 Jul 2008 11:42:21 -0700 (PDT), DeanL <de************ *@yahoo.com>
wrote:
>Hi guys,

Does anyone know of a way to create multiple tables using information
stored in one table? I have a table with 4 columns (TableName,
ColumnName, DataType, DataSize) and wanted to know if there is a way
to use the information in this table to create the many tables that
are listed in the source table instead of creating each table
individually ?

Many thanks for any help you can offer.

Dean...
I use 3 tables to create a database containing temporary tables I sometimes use
for reporting. You may be able to use parts of the code below to achieve what
you want.

1. abfTempTableNam es
TempTableName - Text (PK)

2. abfTempTableFie ldNames
TempTableName - Text (PK)
FieldName - Text (PK)
FieldType - Long
FieldSize - Integer
AutoNum - Y/N

3. abfTempTableInd exes
TempIndexID - AutoNum (PK)
TempTableName - Text
Fields - Text (Format = +FieldName for single field index
or +FieldName1+Fie ldName2+FieldNa me3 for multi field index)
IgnoreNulls - Y/N
Name - Text
Primary - Y/N
Required - Y/N
Unique - Y/N

'************** *************** *************** *************** **********
Function fCreateTempDB() As Boolean
Dim BFws As Workspace
Dim Bfdb As Database
Dim CurDB As Database
Dim prpLoop As Property
Dim strNewDB As String
Dim rstTbls As Recordset
Dim rstFlds As Recordset
Dim rstInds As Recordset
Dim tdf As TableDef
Dim tdfNew As TableDef
Dim strSQL As String
Dim fldNew As Field
Dim indNew As Index
Dim varRet As Variant
Dim strFields As String
Dim x1 As Integer
Dim x2 As Integer
Dim strNewField As String

On Error GoTo HandleIt

'get table/field definitions
Set CurDB = DBEngine(0)(0)
Set rstTbls = CurDB.OpenRecor dset("abfTempTa bleNames", dbOpenSnapshot)

'get current path and set DB name from constant
strNewDB = Mid(CurDB.Name, 1, Len(CurDB.Name) - 4) & "_tmp" & right(CurDB.Nam e,
4)

' Get default Workspace.
Set BFws = DBEngine.Worksp aces(0)

' Make sure there isn't already a file with the name of
' the new database.
If Dir(strNewDB) <"" Then Kill strNewDB

' Create a new database
Set Bfdb = BFws.CreateData base(strNewDB, dbLangGeneral)

'create tables
With rstTbls
If Not (.BOF And .EOF) Then
'set status line
varRet = SysCmd(acSysCmd SetStatus, "Now Creating Temporary File.")
Do Until .EOF
'add tabledef
Set tdf = Bfdb.CreateTabl eDef(!TempTable Name)

'add fields
strSQL = "SELECT * FROM abfTempTableFie ldNames WHERE
(((TempTableNam e)='" & !TempTableName & "'));"
Set rstFlds = CurDB.OpenRecor dset(strSQL, dbOpenSnapshot)

With rstFlds
If Not (.BOF And .EOF) Then
Do Until .EOF
Set fldNew = tdf.CreateField (!FieldName, !FieldType,
!FieldSize)
If !AutoNum = True Then
fldNew.Attribut es = dbAutoIncrField
End If
tdf.Fields.Appe nd fldNew
.MoveNext
Loop
End If
.Close
End With
Set rstFlds = Nothing

'add indexes
strSQL = "SELECT * FROM abfTempTableInd exes WHERE
(((TempTableNam e)='" & !TempTableName & "'));"
Set rstInds = CurDB.OpenRecor dset(strSQL, dbOpenSnapshot)

With rstInds
If Not (.BOF And .EOF) Then
Do Until .EOF
'create index
Set indNew = tdf.CreateIndex (!Name)
indNew.IgnoreNu lls = !IgnoreNulls
indNew.Primary = !Primary
indNew.Required = !Required
indNew.Unique = !Unique

'parse Fields property and strip individual field names
'append each field to index fields collection
strFields = rstInds!Fields
Do Until InStr(1, strFields, Chr(43)) = 0
x1 = InStr(1, strFields, Chr(43))
x2 = InStr(2, strFields, Chr(43))
If x2 <0 Then
strNewField = Mid(strFields, x1 + 1, ((x2 - 1) -
(x1 + 1)))
strFields = right(strFields , Len(strFields) -
(x2 - 1))
indNew.Fields.A ppend
indNew.CreateFi eld(strNewField )
Else
strNewField = right(strFields , Len(strFields) -
1)
strFields = ""
indNew.Fields.A ppend
indNew.CreateFi eld(strNewField )
End If
Loop
tdf.Indexes.App end indNew
.MoveNext
Loop
End If
.Close
End With
Set rstInds = Nothing

Bfdb.TableDefs. Append tdf

'link table
CurDB.TableDefs .Refresh
If (fCheckLink(tdf .Name)) Then 'already linked so delete and
refresh
Set tdfNew = CurDB.TableDefs (tdf.Name)
CurDB.TableDefs .Delete tdfNew.Name
CurDB.TableDefs .Refresh
Set tdfNew = CurDB.CreateTab leDef(tdf.Name)
tdfNew.Connect = ";Database= " & Bfdb.Name
tdfNew.SourceTa bleName = tdf.Name
CurDB.TableDefs .Append tdfNew
tdfNew.RefreshL ink
Else
'connect here
Set tdfNew = CurDB.CreateTab leDef(tdf.Name)
tdfNew.Connect = ";Database= " & Bfdb.Name
tdfNew.SourceTa bleName = tdf.Name
CurDB.TableDefs .Append tdfNew
tdfNew.RefreshL ink
End If

.MoveNext
Loop
End If
End With

'success
fCreateTempDB = True

OutHere:
varRet = SysCmd(acSysCmd ClearStatus)
Bfdb.Close
Set Bfdb = Nothing
Set BFws = Nothing
Set CurDB = Nothing
Exit Function

HandleIt:
Select Case Err
Case 0, 91
Resume Next
Case 75
fCreateTempDB = False
Resume OutHere
Case Else
Beep
MsgBox Err & " " & Err.Description , vbCritical + vbOKOnly,
"fCreateTem pDB"
fCreateTempDB = False
Resume OutHere
End Select

End Function

'************** *************** *************** *************** **********
Function fCheckLink(strT ableName As String) As Boolean
'check if passed table name exists in DB
Dim i As Integer
Dim Bfdb As Database

On Error GoTo HandleIt

Set Bfdb = DBEngine(0)(0)
Bfdb.TableDefs. Refresh

fCheckLink = False

For i = 0 To Bfdb.TableDefs. Count - 1
If Bfdb.TableDefs( i).Name = strTableName Then
fCheckLink = True
Exit For
End If
Next i

OutHere:
Set Bfdb = Nothing
Exit Function

HandleIt:
Select Case Err
Case Else
fCheckLink = False
Resume OutHere
End Select

End Function
'************** *************** *************** *************** **********
Wayne Gillespie
Gosford NSW Australia
Jul 16 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2805
by: Guy Deprez | last post by:
Hi, i'm having a problem to create indexes. STEP 1 ----------- Connection is OK (you can find the string at the end of the message) Table ("Couleurs") creation is OK STEP 2. Index Creation
4
19794
by: intl04 | last post by:
How do I create a data input form in Access that is external to the Access database to which it's connected (if that's possible, which I believe it is)? For example, if someone clicks on an Access file icon on a computer desktop, it will open up a data entry form (that was created in Access). That way, the people who enter the data won't have...
10
3046
by: Mark | last post by:
I have a table about people containing 25 fields. The table contains the usual fields - first, last, address, city, state and zip. There is no primary key. These fields all have data with the exception of a few records missing zip. A person may be in one to five records in the database. If a person is in multiple records, the other fields in...
1
3344
by: poohnie08 | last post by:
i have a excel spreadsheet showing staff name, date,work hour, ot hour, slot1, slot2, slot3, slot4 and others). The "()" will keep repeating from day 1 until end of month. eg in excel spreadsheet, ============================================================================== A1 |A2 A3 A4 A5 A6 A7 A8 |A9 A10 ...
8
1388
by: Rob | last post by:
Dear all I'm well into designing my first ever Access database which is currently about 13 megs (.mdb file) containing data on progress and attainment of approx 500 students. What I want to know is this: to make the database available to approx. 30 colleagues on the school intranet, do I need to change it into a .mdw file and give everyone...
0
1048
by: sam | last post by:
I always create one new class for one table in order to connect it via wizard. So,I had created ten classes for ten tables. Can you teache me how to create one new class which it can connect to multiple tables? Thanks in advance.
27
3766
by: max | last post by:
Hello, I am a newbye, and I'm trying to write a simple application. I have five tables with three columns; all tables are identical; I need to change some data in the first table and let VB updates the same data in all other four tables in the right places. I know it would be possible by using the ForeignKeyConstraint object. I have created...
4
3289
by: knix | last post by:
I have this access project consisting of multiple tables that are linked together in a relationship. I would like to migrate the consolidated information through appending in a datasheet form or importing the gathered information . Can anyone please give solutions with my problem? Can I make a datasheet form (consisting fields from multiple...
6
4178
by: nzkks | last post by:
Hi I am using these: ASP.Net 2.0 with VB.Net, Visual Studio 2005, SQL Server 2005 I suspect, there is something missing in BLL class. I created the ASP.Net form also and checked whether it is working or not. When I submit after entering the data, an error comes. Please help me to solve this problem. Thanks
0
7640
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7957
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8000
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5248
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3685
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3673
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2138
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1240
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
975
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.