conversion from access 2000 to 97 problem

[Faroon]

Hi there all you gurus and semi-gurus and soon-to-be-semi-gurus..
First and formost, I am a rear-kicking programmer of sql databases, but I am no where near a beginner of access. I had an access 2000 database with one table, one form, one report, and a few queries (the form is done using vb and sql, like the code below) all good and fine, and ready to go. However a few people are using access 97 and are therefore unable to use my database. I converted my database to 97 format using Tools->Database Utilities->Convert Database->To Prior... and it converted fine, running beautifully on my comp (which only has access 2000). When brought over to the 97 machine, it craps out on the vb code at the line "set conn = CurrentProject.Connection" (a line in the vb code of my data entry form). My code is very simple, just one connection, and one sql query. I was wondering if anyone can help me convert this 2000 code into the 97 code. I heard there is a different way to connect to a database in 97.

--------------------------------------------------------
Private Sub FindRecord_Click()
Dim conn As Connection
Dim rs As New Recordset

Set conn = CurrentProject.Connection
Set rs = conn.Execute("SELECT * FROM [mytable] ORDER BY
[col1]&quot
MsgBox rs.Fields(0)
rs.Close
conn.Close
--------------------------------------------------------

Thanks in advance. And i notice that the converted 97 file is much smaller than the original 2000 file (2000: 36mb, 97: 500kb). What's up with that? Another thing, what changes do I need to make concerning my reference? Thank you all.

 

[hymn]

try
Dim dbs As Database
Dim rs As Recordset

Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [mytable] ORDER BY [col1]&quot
MsgBox rs.Fields(0)
rs.Close



Hope this helps
Hymn

 

[Faroon]

Thanks alot hymn for the fast reply. Unfortunately I can't test that code yet since I don't have 97 on my machine right now. I should have it soon (I can install it right now, but the cd is mine and the company doesn't let me use it.. something to do with copyright and licensing stuff.. ridiculous law). But anyways ya, the code looks great, for sure I'll try that once I get my 97. Thanks again.