Help with database..

[Andrew Herrmann]

I'm really new to this database stuff (I just started playing with it today). I've created a table with the code below, but I have no idea how to set the values of each field or how to add additional rows into the table. Any help would be appreciated!

Dim wrkDefault As Workspace
Dim dbsNew As Database
Dim tdfNew As TableDef
Dim Index As Integer
Dim TempStr As String

Dim rstMessage As Recordset
Dim fldData As Field

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)

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

' Create a new encrypted database with the specified
' collating order.
Set dbsNew = wrkDefault.CreateDatabase("NewDB.mdb", _
dbLangGeneral & ";pwd=forbidden", dbEncrypt)

Set tdfNew = dbsNew.CreateTableDef("Message Data")


With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' new database.
.Fields.Append .CreateField("OpCpde", dbByte, 1)
.Fields.Append .CreateField("Length", dbByte, 1)

For Index = 1 To 40
TempStr = "DB" & CStr(Index)
.Fields.Append .CreateField(TempStr, dbByte, 1)
Next Index

End With

dbsNew.TableDefs.Append tdfNew

dbsNew.Close

[willem]

Look for workspace, database or recordset in VB help; I had the same questions you have and I found a lot of help on this subject, including working examples.

Success!

[Andrew Herrmann]

Maybe I should have been more specific.. The only way that I can see to "work" with the database is to use the "recordset" object. However, when I use the following code, I get a run time error 13 (type mismatch) on the OpenRecordset command... I've tried this will all sorts of options, but nothing seems to help me...

Set dbsNew = wrkDefault.OpenDatabase("NewDB.mdb", _
dbDriverNoPrompt, False, ";pwd=forbidden")

Set rstMessage = dbsNew.OpenRecordset("Message Data")

[Andrew Herrmann]

I was able to solve the problem.. Below is my response to someone else who had the same problem. Good luck!

----

I was banging my head against the wall on this one for a while. I did a lot of searching on the net and I came up with two things (I'm not sure which one did the trick, but it now works for me).
1) When you set up your recordset, specify DAO. For example:

dim rstNew as DAO.recordset

From what I understand, DAO isn't the first type that VB searches for. If you explicitly state that the recordset is a DAO type, there is no question.

2)I initially had a reference to the DAO 3.51 object library. I changed this to the DAO 2.5/3.51 Compatibility library.

I hope that this does the trick for you!