by Murali Bala (7 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 5.0
Difficulty: Unknown Difficulty
Originally Published: Sat 12th October 2002
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Simple way to create Acess Database, Tables and Fields.
API Declarations
Private wrkDefault As Workspace
Private dbsNew As Database
Private tdfNew As New TableDef
Private cnn As New ADODB.Connection
Private cmd As New ADODB.Command
Private strcnn As String
Private rs As New ADODB.Recordset
Private sDataBaseName As String
VBcoders
Private Sub CreateDB()
On Error GoTo err
sDataBaseName = "c:\NewDB.mdb"
Set wrkDefault = DBEngine.Workspaces(0)
Set dbsNew = wrkDefault.CreateDatabase(sDataBaseName, _
dbLangGeneral, dbEncrypt) 'Create New DataBase
Set dbsNew = wrkDefault.OpenDatabase(sDataBaseName, True, False) 'open the created Database
Set tdfNew = dbsNew.CreateTableDef("NewTable") 'Create Table
With tdfNew
.fields.Append .CreateField("NewField", dbtext) 'Create a new field with text as data type
End With
dbsNew.TableDefs.Append tdfNew 'add the table to the DB
dbsNew.Close 'close database
err:
If err.Number = 3204 Then 'DB exists
Kill sDataBaseName
Resume
End If
End Sub
Private Sub populateDB()
cnn.ConnectionString = "ODBC;DBQ=" & sDataBaseName & ";UID=;PWD=;Driver={Microsoft Access Driver (*.mdb)}"
cnn.open
Set rs = New ADODB.Recordset
strcnn = "SELECT * FROM NewTable"
rs.open strcnn, cnn, adOpenDynamic, adLockOptimistic
rs.addNew
rs.fields("NewField").Value = "Hello"
rs.Update
' To read from the DB into say a textbox named text1
' text1.text=rs.fields("NewField").Value
rs.Close
Set rs = Nothing
cnn.Close
MsgBox "Done. Database can be found at " & sDataBaseName, vbInformation + vbOKOnly
End Sub
