Simple queries in VBA/Access (Access 2000 SR1)

Topic

New Reply

Here’s another newbie question. I know I can do this with DAO, but I suspect that I’m making things too complicated.
I have a table (Contracts) that has a primary numeric key, called (of all things) Primary Key. Is there a simple way in VBA that I can check to see if a record exists in that table, using the Primary Key?

While I’m here – I want to thank everyone for being so patient. I’ve learned a lot from just reading posts, and when I post, the people that answer are always extremely helpful. Thanks!

Brian

Reply | Quote

Replies

Place the following in an SQL view of a query:

SELECT Contacts.*
FROM Contacts
WHERE (((Contacts.PrimaryKey)=111));

Then switch back to the query grid and change the 111 to the primary key you are looking for.

hth

Reply | Quote

Thanks, however I think I found a different way. Access is still new to me, but I can do VB. I finally located the DLookup method, and it gives me what I wanted.

I do appreciate the fast reply.

Brian

Reply | Quote

Just an FYI, DLookup does work well in this situation, but it can really slow down a process if you using it repeatedly, like in a loop.

An SQL statement was already posted on your first post, it would be a good idea to get into the habit of using SQL statements for record interactions. They are the fastest solution.

SQL, or Structured Query Language is actually not too difficult to pick up…at least the basics.

Essentially each ‘line’ in an SQL statement serves a purpose. The most basic query requires a Select and From statement. Select tells the database engine what ‘fields’ you are looking for, and From tells the db engine what table to look in.

Select MyField1, MyField2
From tblMyTable

If you add a Where statement, obviously it puts criteria into the mix:

Select MyField1, MyField2
From tblMyTable
Where MyField3=2

Note that a field used in the Where statement does not have to be in the Select statement.

Access 97 has some wonderful help files on how to use the various SQL statements, however, personally, I just use the query builder to build the query I want, then go into SQL view and copy the SQL code. (Access formalizes a lot of things, so the query above would look more complex then what I wrote, but they both work).

Reply | Quote