Access.2007.VBA.Bibl..

Dim strDBName As String

Dim strDBNameAndPath As String

Dim strPrompt As String

Dim strTitle As String

Dim strTable As String

Dim strCurrentPath As String

Dim fso As New Scripting.FileSystemObject

Dim fil As Scripting.File

strCurrentPath = Application.CurrentProject.Path & “\”

strDBName = “Northwind 2007.accdb”

strDBNameAndPath = strCurrentPath & strDBName

Attempt to find database, and put up a message if it is not found.

Set fil = fso.GetFile(strDBNameAndPath)

If fil Is Nothing Then

strPrompt = “Can’t find “ & strDBName & “ in “ _

& strCurrentPath

& “; to create this database, double-” _

& “click the Northwind.accdt template in the “ _

& “C:\Program Files\Microsoft

Office\Templates\1033\Access folder”

MsgBox strPrompt, vbCritical + vbOKOnly

GoTo ErrorHandlerExit

End If

On Error GoTo ErrorHandler

Set dbsNorthwind = OpenDatabase(Name:=strDBNameAndPath)

NameNewTable:

strPrompt = “Please enter new table name”

strTitle = “Table name”

strTable = InputBox(prompt:=strPrompt, Title:=strTitle, _

Default:=”tblNew”)

With dbsNorthwind

Create new table.

Set tdfNew = _

dbsNorthwind.CreateTableDef(Name:=strTable)

Create fields and append them to new table.

With tdfNew

Set fld = .CreateField(Name:=”EmployeeID”, _

Type:=dbLong)

Working with Access Data 5