how to make sure database is open in procedure

[Guest]

New to writing VBA code and am stumped in trying to figure out why the error
message keeps saying, "Could not find file "General Thoracic.mdb" when the
file is obivously open because I am switching to the VBA editor using the
Alt+F11 toggle. Here is the code:
(by the way, it's probably obvious, but the code is a hodgepodge of cut and
paste)

'this procedure creates a database
Sub CreateTableDefX()

Dim dbsGeneralThoracic As Database
Dim tdfNew As TableDef
Dim prpLoop As Property
Dim fldName As Field
Dim recno As Long
Set dbsGeneralThoracic = CurrentDb
Set dbsGeneralThoracic = OpenDatabase("General Thoracic.mdb")
DoCmd.OpenQuery "acqryProcSpecImport"


' Create a new TableDef object.
Set tdfNew = dbsGeneralThoracic.CreateTableDef("tblProcedures")
recno = 1

Do While recno < 125
DoCmd.GoToRecord acqryProcSpecImport, "Procedure", acGoTo, recno
Set fldName = Procedure
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
' GeneralThoracic database.
.Fields.Append .CreateField("fldName", dbText)

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.

dbsGeneralThoracic.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

recno = recno + 1

Loop


End Sub

 

[John Vinson]

New to writing VBA code and am stumped in trying to figure out why the error
message keeps saying, "Could not find file "General Thoracic.mdb" when the
file is obivously open because I am switching to the VBA editor using the
Alt+F11 toggle. Here is the code:
(by the way, it's probably obvious, but the code is a hodgepodge of cut and
paste)

'this procedure creates a database
Sub CreateTableDefX()

Dim dbsGeneralThoracic As Database
Dim tdfNew As TableDef
Dim prpLoop As Property
Dim fldName As Field
Dim recno As Long
Set dbsGeneralThoracic = CurrentDb
Set dbsGeneralThoracic = OpenDatabase("General Thoracic.mdb")

Simply remove this line.

You already have a pointer to the General Thoracic database using
CurrentDb (I'm presuming that the code is running in this
database...); it's not necessary to set the variable
dbsGeneralThoracic twice.

John W. Vinson[MVP]

 

[Douglas J. Steele]

Since you aren't supplying a full path to the MDB file, Access is looking
for it in the current directory. If it's not there, it's going to tell you
it can't find it.

Either provide the full path, or make sure that the current directory is the
same folder where the file exists.

There's no reason for the two separate Set dbsGeneralThoracic statements,
though.

 

[Guest]

Thanks, I added the full path and got where I needed to go.
Next question, when I debug the code and get to the:
"Set fldName = Procedure"
line, I get the error message:
Run time error 424:
and help on that states:
"References to properties and methods often require an explicit object
qualifier"
So what object qualifier am I missing?
Tx
Rocky

 

[Douglas J Steele]

Sorry, I don't understand what that line of code is supposed to be doing.