Task: Simple way to create Access Database, Tables and Fields.


'Declarations
option explicit
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

'Code:
'******--------PS: Reference ADO 2.5 and DAO 3.6***********-

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


'Declarations
'the correctly functionality of code is necessary activate in references of proyect the Microsoft DAO 3.51 LIBRARY
Public db As Database

'Code:
'databas as path of Database

Function OpenDB()As Boolean
On Error GoTo Erroropening
Set db = DBEngine.Workspaces(0).OpenDatabase(databas)
OpenDB = True
Exit Function
Erroropening:
OpenDB = False
MsgBox "Error: " & Err.Number & " - " & Err.Description
End Function

Function CloseDB()As Boolean
On Error GoTo Errorclose
db.Close
Set db = Nothing
closeDB = True
Exit Function
ErrorClose:
CloseDB = False
End Function

Related Posts

  • Check In: The Greasy Strangler (2016)July 30, 2017 Check In: The Greasy Strangler (2016) Check In: The Greasy Strangler (2016) http://imdb.com/rg/an_share/title/title/tt4381236/
  • April 4, 2008 S6301554
  • Lord Byron interludeApril 5, 2000 Lord Byron interlude Apr 5, 2000 SHE walks in beauty like the night Of cloudless climes and starry skies, […]
  • November 29, 2011
  • 9289 – WednesdayMay 22, 2008 9289 – Wednesday 9289 - Wednesday Pyewacket says, “Soon.. it will be my turn in the sun. Soon.” tackle […]

Leave a Reply