Creating tables in VBA

[lth]

Hello. I would very much like to be able to create tables in Access's VB, beyond the use of 'runcommand acCmdNewObjectTable', ie. I would like to be able to specify, in VB, the attributes of each field. Is this even possible in Access or should I go use MySQL/C++?

Thanks a lot.

Kendrick

 

[mp9]

You can do this with SQL along the lines of CREATE TABLE... the benefit of this being that you can run the code from within your VBA or as a standalone Access query.

The syntax takes the form:

CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

Search Access help for "CREATE TABLE statement" for more info.

http://www22.brinkster.com/accessory/

 

[cmmrfrds]

Using DDL directly in an SQL statement is a good way to go. There are a couple of additional ways using either the DAO library or the ADO library. DAO is generally restricted to Access but ADO can be used from any client that supports the ADO library, such as, Access, ASP, various C++ products and others.

Here is a function that does some things in ADO including creating a table.


Function catalogInfo()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As ADOX.Column
Set cg.ActiveConnection = CurrentProject.Connection

tb.Name = "Test"
tb.Columns.Append "col1", adInteger
tb.Columns.Append "col2", adVarWChar, 50
''Debug.Print "table = "; cg.Tables("Test&quot.Name
cg.Tables.Append tb
''Exit Function
'-rename a column
''Set tb = cg("test&quot
''Set cl = tb("col2&quot
''cl.Name = "col2aa"

Exit Function

Set cg.ActiveConnection = CurrentProject.Connection
Set tb = cg.Tables("dbo_categories&quot
Debug.Print "table name = "; tb.Name
'''Set cg.Procedures("myproc&quot = "select * from customer"
''Dim pp As Property
''Debug.Print "column = "; tb.Columns("Description&quot.Properties("default&quot.Value
''Exit Function
For Each cl In tb.Columns
Debug.Print "name = "; cl.Name
Debug.Print "type = "; cl.Type
''For Each pp In cl.Properties
'' Debug.Print "property name = "; pp.Name
'' Debug.Print "property value = "; pp.Value
''
''Next
Next

End Function

Here is a link that has some good examples of DAO and ADO code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp

 

[lth]

Thank you both kindly, I will investigate

 

[lth]

mp9 -

Investigating your method turns up the following chunk of code:

Dim dbs As Database

Set dbs = OpenDatabase(CurrentDb)

dbs.Execute "CREATE TABLE ThisTable " & "(FirstName CHAR, LastName CHAR);"

dbs.Close

(Dumb newbie question): Database is not recognized by VBA as a valid type - it throws up the error 'User defined type not defined'... Now, I would have hoped that VBA would know what a database was, but obviously not... what the hell? How do I fix this?

Thanks

LTH

 

[mp9]

You need to declare a refernce to DAO 3.6 - from the VBA editor window, select Tools, References. Then browse to and add "Microsoft DAO 3.6 Object Library" (on my PC it's in C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll if that's any help).

Then, using the up and down arrows, ensure that this reference is above any references you might have to ADO objects.

This should do the trick.

http://www22.brinkster.com/accessory/

 

[lth]

Yeah, I just found that out myself.

*kicks Access*

Thanks a lot