Access: Generate Table From Form

[egotek]

I need to be able to generate/create a new table from a number of text boxes on a form, with the push of a button, if possible. I really don't even know where to start, i looked through the Access Help, but didn't find anything relating to that. I want to be able to specify the name of the table, fields and information for the fields.


TIA,

Michael

 

[dhookom]

This is a fairly broad question since we don't know what you understand about DAO or TableDefs or ... Below is some code that I use to create a table, fields, and indexes in a database.
Function CreateAdmins() As Boolean
On Error GoTo errCreateAdmin
Dim db As DAO.Database
Dim tdf As DAO.TableDef
'Dim fld As DAO.Field
'Dim ndx As DAO.Index
Dim strRemoteDB As String
Dim strSQL As String
Dim strTableName As String
CreateAdmins = True
strTableName = "tblSecurity"
Set db = CurrentDb
Set tdf = db.CreateTableDef(strTableName)
With tdf
.Fields.Append .CreateField("admAdmID", dbLong)
.Fields("admAdmID&quot.Attributes = dbAutoIncrField
.Fields.Append .CreateField("admSecLevel", dbInteger)
.Fields.Append .CreateField("admLogin", dbText, 20)
db.TableDefs.Append tdf
db.TableDefs.Refresh
End With
strSQL = "CREATE UNIQUE INDEX PriKey ON " & strTableName & " (admAdmID) WITH PRIMARY"
db.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO tblSecurity (admSecLevel, admLogin) Values (3, 'thomsom')"
db.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO tblSecurity (admSecLevel, admLogin) Values (3, '" & fOSUserName() & "')"
db.Execute strSQL, dbFailOnError

exitCreateAdmin:
On Error Resume Next
Set tdf = Nothing
Set db = CurrentDb
Exit Function

errCreateAdmin:
Select Case Err
Case Else
End Select
CreateAdmins = False
Resume exitCreateAdmin

End Function


Duane
MS Access MVP

 

[egotek]

Yeah, it is a pretty broad request, but didn't really know where to start. Thanks for the reply though. In doing a few google searches I've found code similar to that, but each time I try to put it in my code just to test it out I always get and error on the first line:
db As DAO.Database "User-defined type not defined"

Other code I've found may just have 'DIM db As Database' and I get the same error on that.

Any thoughts?

Just for reference, I'm not a professional programmer or anything I use Access when I HAVE to, haha. I have a very small VB background. I can USE Access and create standard functions with in it, but I have a hard time generating code from scratch. Just need something to get me started!

Thanks again,
michael

 

[dhookom]

You are probably using Access 2000 or 2002. You need to set a reference to the DAO library. Open any module and select Tools|References. Then scroll down to find something like "Microsoft DAO ... Object Library". Check it and close the references. Now try to debug/compile your project.

You should then be able to use the DAO objects in code...

Duane
MS Access MVP

 

[egotek]

ahhh, OK. That did it, still got an error in the code, but i'll try to work that out. Thank you very much!!