I have been trying to access an external data source using ADO, but everytime
I run the code I get an error message saying the:

"Dim cn as New ADODB.Connection" is not defined.

Can anyone help, I am new to ADO so it will probably be a simple answer.

you don't need the "new" keyword...and it is recommend you don't use "new"
anway....

'An example DAO vs ADO recordset loop, you'll see how similar they are:

'--- begin DAO ---
Dim rst As dao.Recordset
Set rst = CurrentDb.OpenRecordset("select * from contacts")
Do While rst.EOF = False
Debug.Print rst!FirstName
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
'--- end DAO ---

'--- begin ADO ---
Dim rs As ADODB.Recordset

set rs = new ADODB.Recordset ' use the new in code...not at dim
time....
rs.Open ("select * from contacts"), CurrentProject.Connection
Do While rs.EOF = False
Debug.Print rs!FirstName
rs.MoveNext
Loop
rs.Close
Set rs = Nothing


Of couse...in the above example, we are using the built-in connection object
for ADO....

you could deifne that conenciton object....

Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
With rs
rs.Open "MyTable", cn, adOpenDynamic, adLockOptimistic, adCmdText
If Not .EOF Then
.MoveFirst
Do Until .EOF
Debug.Print !MyField
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing

Of couse, to connect to sql server...you need to go

cn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"

You don't mention what kind of external data source you are talking
about......

....perhahaps a extrenal mdb database?

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\myDb.mdb;"
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.members.shaw.ca/AlbertKallal

I still get the error message. I am using a dsn to connect to a mysql
database on a server. It works okay with DOA however, I have to run some
server side procedures, so I wanted to try ADO. Could there be a problem
with my Object library references?

"Albert D.Kallal" wrote:

> you don't need the "new" keyword...and it is recommend you don't use "new"
> anway....
>
> 'An example DAO vs ADO recordset loop, you'll see how similar they are:
>
> '--- begin DAO ---
> Dim rst As dao.Recordset
> Set rst = CurrentDb.OpenRecordset("select * from contacts")
> Do While rst.EOF = False
> Debug.Print rst!FirstName
> rst.MoveNext
> Loop
> rst.Close
> Set rst = Nothing
> '--- end DAO ---
>
> '--- begin ADO ---
> Dim rs As ADODB.Recordset
>
> set rs = new ADODB.Recordset ' use the new in code...not at dim
> time....
> rs.Open ("select * from contacts"), CurrentProject.Connection
> Do While rs.EOF = False
> Debug.Print rs!FirstName
> rs.MoveNext
> Loop
> rs.Close
> Set rs = Nothing
>
>
> Of couse...in the above example, we are using the built-in connection object
> for ADO....
>
> you could deifne that conenciton object....
>
> Dim cn As ADODB.Connection, rs As ADODB.Recordset
> Set cn = CurrentProject.Connection
> Set rs = New ADODB.Recordset
> With rs
> rs.Open "MyTable", cn, adOpenDynamic, adLockOptimistic, adCmdText
> If Not .EOF Then
> .MoveFirst
> Do Until .EOF
> Debug.Print !MyField
> .MoveNext
> Loop
> End If
> .Close
> End With
> Set rs = Nothing
> Set db = Nothing
>
> Of couse, to connect to sql server...you need to go
>
> cn.Open "Provider=sqloledb;" & _
> "Data Source=myServerName;" & _
> "Initial Catalog=myDatabaseName;" & _
> "User Id=myUsername;" & _
> "Password=myPassword"
>
> You don't mention what kind of external data source you are talking
> about......
>
> ....perhahaps a extrenal mdb database?
>
> oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=c:\somepath\myDb.mdb;"
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
> http://www.members.shaw.ca/AlbertKallal
>
>
>
>

Per itHeiner:
>I still get the error message. I am using a dsn to connect to a mysql
>database on a server. It works okay with DOA however, I have to run some
>server side procedures, so I wanted to try ADO. Could there be a problem
>with my Object library references?

That's the first place I'd look.

Just checked my last SQL Server app and it has a ref to
"Microsoft ActiveX Data Objects 2.5 Library", which points to
C:\Program Files\Common Files\System\ado\msado25.tlb"
--
PeteCresswell

Well, any reason why you don't just link a table via odbc...and use that?

I was not aware that mysql has any server side procedures you can run?

Further, I also am not aware that MySql as has a oledb (ADO) provider...I
don't believe it does....you have to use odbc....

(so, this means using the ADO methods to execute stored code on the server
side does NOT apply..since mysql does not have stored procedures).

ADO does support odbc, and thus you can use it if you don't have a oledb
provider (as I mentioned, I don't think MySql does).

Check with the MySql people as to what the connection string looks like,
(and, while you are at it..ask them if there is a oleDB provder...or do you
have to use odbc?).

So, you *can* use ADO here...but will be doing so through the odbc provder
for ADO..and that don't support the execute methods to run stored procs....

Howwever, you can certanly send ANY commad to MySql with a pass-through
query....include ones that would read sql scripts (you don't need ADO to do
this....).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.members.shaw.ca/AlbertKallal