Access and VBA Select issue

[jalldridge]

Hi guys

I want to be able to run a select query such as:

select distinct email_address from tbl_download where email_addres='fred@xyz.com'

in VBA code in access. I then want to be able to see if my result set contains any info ie there way an email address. If yes I will do one thing, if no another.

However as yet I've not found a way in access to run a select statment and get hold of the resultset.

Any pointers greatly appreciated. Heres what I tried to date :-(

Set rst = New ADODB.Recordset
'rst.CursorType =adOpenDynamic
rst.Open "select distinct email_address from tbl_download where email_addres='fred@xyz.com'", curconn, , , adCmdText
srtv = rst![email_Address]

rst.Close

If I run this I get an error at the open command that states

the database has been placed in a state bu user admin on machine XX that prevents it from being opened or locked.

I managed to do an update ok by using docmd.runsql but couldnt find a docmd.method for running select command

Thanks

 

[Mile-O]

For a start, put square brackets around each table and field since you are using special characters in their names.

 

[jalldridge]

OK managed to get this to work. Ta.

So a question on the same line:

I'm in my access database and with the code mentioned above am creating another connection to the same database and then pulling out the info I'm after. I'm then closing the connection.

I dont have to do something similar for updates or inserts (I just use doCmd.RunSQL), so I assume that there must be some way of doing a select and getting hold of the result set without creating a new connection. Correct? If so how is this done? It doesnt seem to efficient to have to create connections all the time...

Thanks again

 

[WayneRyan]

j,

You're retrieving an address that you already know!

If you just want to check for something:

If Dcount("[email_address]", "tbl_download", "[email_address] = 'fred@xyz.com'") > 0 Then

If you want to retrieve a field from a table:

SomeField = Nz(DLookUp("[email_address]", "tbl_download", "[UserName] = 'fred'"))

Wayne

 

[jalldridge]

Thanks Wayne,

Didnt know about those functions. Should be able to get them to do the trick!!!!