dbFailOnError causing varible error.

[Fred Wilson]

The following code works as long as I do NOT include the option
"dbFailOnError" it gives be "Variable not Defined"

Any ideas?

I want the option just to cover myself in the event of an error.


Function UploadTables()

Dim strLoc As String

Dim strSQl As String

strLoc = "\\SERVER\DIRECTORY\FILE_DIRECTORY\FILE_BE.mdb"

strSQl = "Select * Into tblJunk from tblfacility IN '" & strLoc & "';"

CurrentDb.Execute strSQl, dbfailonerror

End Function

 

[Graham Mandeno]

Hi Fred

The constant dbFailOnError is defined in the DAO object library. Probably
you do not have a reference to this library (Access 2000 changed the default
reference to ADO).

Go to Tools>References from the VBE code window and click the checkbox next
to "Microsoft DAO 3.x Object Library". If you are not using ADO at all in
your code, you should also uncheck "Microsoft ActiveX Data Objects 2.x
Library".

 

[Frederick Wilson]

OH BOY, the whole ADO DAO thing. I can not keep one from another. How do
I know whether or not I have any code that is DAO. I guess if this
failure is any indication I have not DAO code anywhere.

What would I use to make CurrentDB.Execute an ADO statement?

Thanks,
Fred

 

[RoyVidar]

Try the execute method of the connection object, for instance

currentproject.connection.execute strSQl

or

dim cn as adodb.connection
set cn=currentproject.connection
' or open a connection to the database where you wish to insert this
' see
'http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJet
' watch linebreaks in the link
cn.execute strsql

CurrentDB is, as I've understood it, a reference to the current DAO
database
object

 

[Fred Wilson]

So if I am reading all of this correctly, there is NOT a dbFailOnError
for ADO?

 

[RoyVidar]

I think that might depend on what you rely on dbFailOnError for. If
it's
rolling back in case of error, perhaps look into transactions, if it
is
getting the error message(s), check into looping the errors collection
(retrieve it through the connection) - but I'm not using DAO much,
so...

 

[Graham Mandeno]

Hi Fred

The easiest way to see if you have any ADO code is to uncheck the reference
and see if your code compiles.

If your database is purely Jet (MDB with linked MDB tables) then DAO is more
efficient than ADO.

 

[Fred Wilson]

Roy,

Thanks for your input. I will look into the transaction thing. I've
never heard of it so if you can give me a head start on where to look,
it would be much appreciated.

Fred

 

[RoyVidar]

I really know to little about transactions, but until someone
with experience shows up, or you find some infor yourself,
I think the following hopefully gives a starting point.

Initialize/assign cn as your connection variable

cn.begintrans
cn.execute <some sql>
' more stuff?

if <some condition> then
cn.committrans
else
cn.rollbacktrans
end if

There's a more comprehensive sample in the help files, and I guess
a search through the newsgroup would also give some information.

But do also take Graham Mandeno's advice into consideration
(using DAO in stead of ADO), if you're only working with Jet.