Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Why is Access VBA syntax different from Excel syntax?


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!!!!




  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,565
Default Why is Access VBA syntax different from Excel syntax?

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.


"cr113" wrote in message
...

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!!!!






  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Why is Access VBA syntax different from Excel syntax?

On Dec 1, 5:45*pm, "JLGWhiz" wrote:
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
syntax error in VBA in query from Access Winget Excel Discussion (Misc queries) 0 January 21st 10 08:49 PM
Syntax to address ThisWorkbook.BuiltinDocumentProperties via Access Automation? [email protected] Excel Discussion (Misc queries) 0 June 24th 08 01:29 AM
Excel VB - WHERE syntax error in Access web query. [email protected] Excel Programming 1 October 20th 06 07:05 PM
SQL syntax in VBA for Access kohai Excel Programming 2 July 11th 05 05:45 PM
Microsoft][ODBC SQL Server Driver]Syntax error or access violation Jordan Excel Discussion (Misc queries) 0 July 6th 05 06:58 PM


All times are GMT +1. The time now is 04:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"