Creating a new table

[DogLover2006]

I have this code and it ran once not it give me the following message that it can not find TempFederal.mdb. Could some on help me I am using access 2002.

Public Sub CreatesNewTable()
Dim dbsTempFederal As Database
Dim tdfNew As TableDef
Dim prpLoop As Property

Set dbsTempFederal = OpenDatabase("TempFederal.mdb")

' Create a new TableDef object.
Set tdfNew = dbsTempFederal.CreateTableDef("NewYear")

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
' Northwind database.
.Fields.Append .CreateField("FirstName", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)

Debug.Print "Properties of new TableDef object " & _
"before appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
' For Each prpLoop In .Properties
' On Error Resume Next
' If prpLoop <> "" Then Debug.Print " " & _
' prpLoop.Name & " = " & prpLoop
' On Error GoTo 0
' Next prpLoop

' Append the new TableDef object to the Northwind
' database.
dbsTempFederal.TableDefs.Append tdfNew

Debug.Print "Properties of new TableDef object " & _
"after appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
' For Each prpLoop In .Properties
' On Error Resume Next
' If prpLoop <> "" Then Debug.Print " " & _
' prpLoop.Name & " = " & prpLoop
' On Error GoTo 0
' Next prpLoop

End With

' Delete new TableDef object since this is a
' demonstration.
' dbsTempFederal.TableDefs.Delete "Contacts"

dbsTempFederal.Close

End Sub

 

[PHV]

Why not using the full pathname ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jrbarnett]

At the line of code:

Set dbsTempFederal = OpenDatabase("TempFederal.mdb")

the code attempts to open a connection to the database TempFederal.mdb. As no specific path is specified, it must be in the current directory. You can get around this by specifying the actual location of the file, such as:

Set dbsTempFederal = OpenDatabase("C:\TempFederal.mdb")

The file itself must already exist on the disk in that location. If not, create a new blank access database of that name in the folder required.

I can't see any other problems with that code other than the requirement to have a reference to the DAO object library. Its worth trying just setting the path first and seeing if it falls over again later on.

John

 

[DogLover2006]

thanks for your help, The file does exist because it is the current name of database and that code is in a module on that database. That is also why I did not put the path. I did try it with the path and still got the same result.

 

[jrbarnett]

If it is referring to the current database, just use:

Set dbsTempFederal = CurrentDb

John

 

[DogLover2006]

jrbarnett,

Thank you I thought I try that but I guess I did not so thank you for your help it works.