code lines to change when converting from DAO to ADO?

[quipy]

hey,

i've been asking for help regarding the creation of a report from an SQL from a form, the steps i've gotten are used for ADO: myreport.datasource(). so i'm thinking about converting my DAO connection to ADO. i'm not using any data-controls, everything's in code. i was wondering, what DAO-specific lines must i alter? these are some of what i think are DAO codes in my program:

Set DB = OpenDatabase(App.Path & "\Members97.mdb")

Private DB As Database
Private RS As Recordset

Set RS = DB.OpenRecordset()

With RS
.Edit
.Fields("MemSName").Value = txtMemSName.Text
.Update
.Close
End With

anything else i should convert? how about the "project-references" properties?
do i need to put something like: ADODB.conection as a general declaration? also, are SQL statements the same in ADO?

[darre1]

you will need a reference to ADO in your project references.

ADO doesnt use database objects, it used Connections, Commands and Recordsets.

Also SQL should be the same for both.

[alex_read]

Yeah, add the reference to "Microsoft ActiveX Data Objects x.0" object library, then use this :

Private conn As ADODB.Connection
Private RS As ADODB.Recordset 

Set conn = ***
Set RS = "SELECT * FROM Table1", conn

The way you deal with the recordset object once at this point is much the same between ADO & DAO. The *** bit you can cheat on!
1) Create a new txt file on your pc, then rename it to file.udl.
2) Open it up & set the options visually, then save it.
3) Rename the file back to a txt file & paste the created connection string into the above, voila !

[darre1]

Originally posted by alex_read
...The *** bit you can cheat on!
1) Create a new txt file on your pc, then rename it to file.udl.
2) Open it up & set the options visually, then save it.
3) Rename the file back to a txt file & paste the created connection string into the above, voila !

Great tip!!

[alex_read]

I've been coding with ADO for a good 8 months & I'm sat here with 2 thick ADO books in front of me, considering the connection objects the main difference between DAO & ADO, you would have thought I'd have learn't this & wouldn't have to look it up all the time, but nooooo
I still use that way as it's much lazier & easier

[darre1]

i do something similar, i make a connection using the dataenvironment and then copy the connectionstring property

your's is easier and lazier though woohoo!

[quipy]

thanks for your replies... i still don't get that *** part though. how does it work? what's it suppose to achieve? is the "non-cheat" way much more difficult?

[alex_read]

It's letting you visually chose the options to build your querystring, which drivers to use, which locking the recordset should have etc.

If you look at the recordset object in the ADO help, then you can do it manually, but it's a lot more difficult ot understand & plan the options you want.

What part are you stuck with, how far have you got ?!?!

[darre1]

when you want to connect using ADO you have to declare a connection object and then specifiy a connectionstring before opening the connection.

One typo and it won't work, that's all. they're usually quite lengthy, so it's good if you don't have to remember the exact syntax etc.

this is an example of a connection string to connect to an Access database using Jet 4.0 anyway...

Dim cn as Connection
 
Set cn = New Connection
 
cn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; " _
        & "Data Source=[i]DATABASEPATH[/i];"
cn.Open

hope that helps

[crispin]

Alex,


Where did you pull that one from (the udl rename thing)???

Thats top that is!

That'll save me loads of time...


Beers on me!


thx

[alex_read]

Hmm, Cornwall, it'll take a couple of hours, but if it's a free pint then it's worth it

I used to get a load of quizes through in Excel - sort of
Name John Lennons 1980 No1 :
where the answers'll be hidden - renaming files to txt files was a quick hacking technique When I first saw the UDL file I thought whyt not, it might work - renamed it & wayhay, lazy coding !!!

[quipy]

compile error: invalid outside procedure
(reference: Microsoft ActiveX Data Objects 2.0 Library)

(General declarations)
Private conn As ADODB.Connection
Private RS As ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Documents and Settings\Calvin Chong\My Documents\apiit\semester3\pp\coding\testado\Members97.mdb"
conn.Open

---------------------------------------------------------------

Private Sub cmdReport_Click()
Set RS = "SELECT * FROM Members ORDER BY MemNum"
DataReport1.DataSource = RS
DataReport1.Show

End Sub


what's wrong?

[oh1mie]

Originally posted by quipy
compile error: invalid outside procedure
(reference: Microsoft ActiveX Data Objects 2.0 Library)

(General declarations)
Private conn As ADODB.Connection
Private RS As ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Documents and Settings\Calvin Chong\My Documents\apiit\semester3\pp\coding\testado\Members97.mdb"
conn.Open

---------------------------------------------------------------

Private Sub cmdReport_Click()
Set RS = "SELECT * FROM Members ORDER BY MemNum"
DataReport1.DataSource = RS
DataReport1.Show

End Sub


what's wrong?

Where you tell to RS than conn is Rs connection ?
Do Datareport1 need a refresh ?

[Helger]

change that

Private Sub cmdReport_Click()
Set RS = "SELECT * FROM Members ORDER BY MemNum"
DataReport1.DataSource = RS
DataReport1.Show

End Sub

to:

dim rst as adodb.recordset
set rst = new adodb.recordset
rst.activeconnection = conn
rst.source = "SELECT * FROM Members ORDER BY MemNum"
rst.open
datareport1.datasource = rst
datareport1.show