How do I query my Access database?

  • Thread starter Thread starter michaaal
  • Start date Start date
M

michaaal

Could someone past some sample code of how to query my Access database? I
am using VBA through MSAccess.

Thank you!
 
It is not exactly clear what you mean by query?

In general, where do you want the results of the query to go?

A form, a report?

Do you need to do some processing on some records?

It is kind of really depends here. Do you want to write a query to update
data, or pull data (and, pull the data to where?).

You can certainly use the query builder, and also use VBA code to run those
queries.

It is just not quite 100% clear what you want to do here....

If you give just a bit more info on the kind of thing you are tying to do..I
will certainly post a example....
 
Thanks Albert! I'll give more details....

I have a contacts table called "Customer List" that contains these fields:
FirstName, LastName, EmailAddress.
I would like to programmatically lookup the last name "Smith" and then
change Mr. Smith's FirstName from "John" to "Joe".

Thanks Albert!!

Michaaael
 
There is a number of ways to do this.

The first way is to use sql. In code, this would look like:

dim strSql as string

strSql = "update [customer list] set FirstName = 'Joe' where " & _
"LastName = 'Smith' and FirstName = 'John'"

currentdb.Execute strSql

The above will do as you ask. So, often, data changes are thus made using
sql. Combing your knowledge of sql and code can results is very powerful
data management.

Of course, we could also use a old fashioned approach like in the early days
of computing, where we process record by record (we tend NOT to do this
anymore, since using sql can be sent across the country and EXECUTED on the
target machine. Thus, sql is often preferred since it lets us use a client
to server type setup (that sql statement can be executed BY the server if
you are using a database engine like sql-server)..

However, lets write the code to do the above in the old code way. Here is a
code loop that would do the above:

dim rstRecords as dao.RecordSet

set rstRecords = currentdb.OpenRecordSet("[customer list]")

do while rstRecords.Eof = false
' process records while end of file = false

if rstRecords!FirstName = "John" and rstRecords!LastName = "Smith" then
rstRecords.Edit
rstrecords!FirstName = "Joe"
rstRecords.Update
end if
rstRecords.MoveNext
loop

rstRecords.Close
set rstRecords = nothing.

So, the above the does the same thing as our first code..but as you can see,
the first example is far easier to write.

Of course, we can combine both sql, and data processing together. The above
code loop is wasteful, since it has to test ALL records in the table for a
match. A better approach would be to query ONLY the John Smith. Thus, we
should, and could re-write the above as:

dim rstRecords as dao.RecordSet
dim strSql as string

strSql = "select FirstName, LastName from [Customer List] where " & _
"FirstName = 'John" and LastName = 'Smith'"

set rstRecords = currentdb.OpenRecordSet(strSql)

do while rstRecords.Eof = false
' process records while end of file = false
rstRecords.Edit
rstrecords!FirstName = "Joe"
rstRecords.Update
end if
rstRecords.MoveNext
loop

rstRecords.Close
set rstRecords = nothing.

Once again, you can see that using sql generally saves us coding...
 
I also forgot to add that you can skip all of what I showed, and simply use
the query builder.


If you use the query builder, then you can create a query, and not have to
write one line of code.

So, as you can see, this is real big choice of how you can update your data.
So, that is kind of why I asked what are you trying to do..as there is
usually MANY ways to accomplish the same thing. Which way you choose is
going to depend on what you are actually trying to do...
 
Back
Top