Object variable not available in subroutine - how to fix?

E

EagleOne

2003


Had to divide some code to a sub-routine.

In the master code procedure the code-setup is the following:

Sub mySub()
.....
.....

Set appAccess = CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase myPath & dbsfilename
' Get Database object variable.
Set dBs = appAccess.CurrentDb
.....
.....
Call mySubSub()
.....
.....
End Sub



Sub mySubSub()

dBs.Execute "Alter Table ........."

End Sub

When the code dBs.Execute "Alter Table ........." is read, I get a Error 424. Obvious;y,
"dBs" has lost its object meaning in the Called sub-routine.

What is the preferred fix for the above?

I thought about issuing the SET command again, but I think that there must be a better fix.

TIA EagleOne
 
D

Dale Fye

EagleOne,

Pass the reference to the db to the new sub. Dimension it in the
subroutine.

Sub mySubSub(dbs as Object)

dBs.Execute "Alter Table ........."

End Sub

Then, in you main code, just pass the dbs as an argument

Call mySubSub(dbs)

HTH
Dale
 
G

Guest

If all you want is dbs, do it like this:

set dbe = CreateObject("dao.dbengine.36")
set dbs = dbe.OpenDatabase( myPath & dbsfilename )

The dbe object is much smaller and quicker than the
appAccess object. Most of the appAccess methods
are just wrappers for dao.dbengine methods. (and your
example only shows a dbengine method: you are using
Application.dbEngine.databases(0).execute)

(david)
 
E

EagleOne

Really like your idea of using the dbe. I did not realize that differential.

BTW, after previously attempting
1) (ByVal....)
2) (ByRef .......)
3) (AppAccess as "whatever") 'Did not like the "As"; wanted an "="
the only thing which worked was simply:

mySubSub(AppAccess, dBs))

I never quite got the concept of when 1) or 2) or 3) will work in a situation.

Actually, I did NOT try 3) because I initially thought it too simple to work - a classic of the
concept of a little knowledge being dangerous.

Thanks EagleOne
 
E

EagleOne

Dale,

I thought that what I had was correct because it did not error (424) and it seemed to work.

That said, I ended up with your suggestion after wasting an hour with my idea.

Therefore, to pass an Object variable to a subroutine (with my facts):

Sub Main()

Call Secondary(appAccess, dBs)

End Sub

Sub Secondary(appAccess As Access.Application, dBs As DAO.Database)

dbs.Execute "Whatever SQL code"

End Sub

Thanks! EagleOne
 
T

Tony Toews [MVP]

Dale Fye said:
Sub mySubSub(dbs as Object)
Click to expand...

I would've done that as dbs as database so that the VBA intellisense
would work. And to help with any PEBKAC errors. In other words some
extreme foolishness on my part passing in the wrong kind of object.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
J

John Spencer

I have always expanded PEBKAC to

Problem Exists Between Keyboard And Chair

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

TransferSpreadsheet (via VBA) getting : "Read Only" error message 0
Help with Access VBA to create a new file and import textfile data 7
NewAccessDatabase() 1
Set object variable in class module 5
Object Variable Error 7
error 3061 trying to insert a date in a table 0
copy table from closed database 4
Run Access Subroutine (or Function) from Excel 2

Top