Database Stuph

[capone67]

Hi guys

I am trying to build a project that involves a database but I would preffer not to have to include the MS data control on a form. The program will eventually run with no form. Also if it can be avoided I would rather not have to run an install program. I can add and update the database by calling MyConn.Execute MySQL no problem. The problem arises when I try to do a count on the database to see if a record is present. I am getting run-time error 3001 when I try to use a record set variable. I awould like to know how to Dim a record set. I am able to create the connection programmatically but having no luck of creating a recordset programmatically. I would like to do something similar to below.

Set MyConn = CreateObject("ADODB.Connection&quot
MyConn.Open ConnectionString
Dim RS as MyConn.RecordSet
Set RS=MyConn.Execute ("Select Count from Listings&quot

Any ideas how I would do this?

 

[vb5prgrmr]

To start with you SQL is a bit off. It should be...

[tt]Select Count(*) from Listings[/tt]

You could also alias your count if you wanted...

[tt]Select Count(*) As TheCount from Listings[/tt]

[tt]Dim AdoCn As ADODB.Connection
Dim AdoRs As ADODB.RecordSet

'open connection here
Set AdoRs = New ADODB.RecordSet
AdoRs.Open TheSQLString, AdoCn, adOpenDynamic, adLockOptimistic

TheResultOfCount = AdoRs.Fields("TheCount&quot

[/tt]

I Hope this helps