Why is Access VBA syntax different from Excel syntax?

C

cr113

I just started using Access VBA 2003 and I came across 2 very odd
things:

Why is the syntax different from Excel? In my Excel macros I use the
Microsoft ActiveX Data Objects 2.8 Library. Here is a code sample in
Excel:

Dim cnn As New Connection
Dim rst As New Recordset
Dim strSQL As String

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb"
Set rst = Nothing
strSQL = "SELECT * FROM Table1"
rst.Open strSQL, cnn

When I tried this in Access it didn't like the "New" qualifier and
Open was not a function of the recordset class. Why is this? It's the
exact same reference.

Also after I was trying to get the code in Access to work I noticed
that all of my Excel references were changed from Microsoft ActiveX
Data Objects 2.8 Library to Microsoft ActiveX Data Objects 2.1
Library.

Does anyone know what is going on? Help!!!!
 
J

JLGWhiz

As best I can tell, different folks worked on different applications and
each individual tailored the VBA software to their specific application.
You could just as easily wonder why they change the code from release to
release in Excel VBA. Software engineers seem to have this feeling of
ownership about their work assignments and they don't like to give someone
else credit for any of their work, so they try to make everything look like
it is better by making it different. But the real answer is probably that
they were too lazy to research to see if a procedure already existed for
what they were doing.
 
C

cr113

As best I can tell, different folks worked on different applications and
each individual tailored the VBA software to their specific application.
You could just as easily wonder why they change the code from release to
release in Excel VBA.  Software engineers seem to have this feeling of
ownership about their work assignments and they don't like to give someone
else credit for any of their work, so they try to make everything look like
it is better by making it different.  But the real answer is probably that
they were too lazy to research to see if a procedure already existed for

I think I figured it out. Evidently Access VBA already contains
Recordset and Connection objects that are different from ActiveX Data
Objects. When I changed the declaration from Dim rst as Recordset to
Dim rst as ADODB.Recordset, the syntax changed to what I expected it
to be. So in other words there are 2 completely different
Recordset,etc objects.
 

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

Top