+ Reply to Thread
Results 1 to 4 of 4

Query Access Database

  1. #1
    Registered User
    Join Date
    06-13-2006
    Posts
    14

    Query Access Database

    I'm struggling trying to figure out how to write a query via code that will pull data from an Access DB. I have looked at several examples that have already been posted but I'm unable to figure out how to make it work for my needs. I think my problem is I'm not understanding what all is required and what each line of code that I have found is saying.
    I would like to pull data from several different tables in the db based on 2 criteria. 1. a date range and 2. a "station name". So in summary I want data collected betweek xx date and xx date with the station name "line 1".

    Below is some code I have been messing with but get errors when trying to run. I don't think I'm even close. If someone has a basic query they could show me with an explination of what things are or do then I think I can make this work for me.

    This forum has provided me with much help in my quest to learn Excel Programming and I appriciate all the help that has been provided.

    If there is a tutorial or some other instruction out there that you have found to be helpful I'm open for suggestions. I don't mind trying to figure things out on my own but thus far I've been stuck on this one.

    Thanks in Advance for any and all help.

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Have you tried recording a macro whilst you are seting up a New Database Query.

    I find this is usually the best way to start as the macro is recorded to give you what you require

  3. #3
    Registered User
    Join Date
    06-13-2006
    Posts
    14
    Guess I had looked at this way too long... Didn't even think of that.

    Thanks... I'll give it a shot

  4. #4
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    Hi BN,

    Agree with the mudraker on how to get started. Let me add a couple of thoughts.

    It took me a while to understand why the CommandText is always passed as an array. And why the Macro Recorder always seems to break the string up at weird intervals.

    I wrote a few ODBS calls where I did not use an Array and they worked fine. Then I wrote a few more without Arrays and they did not work at all. Turns out the answer is that the CommandText cannot have any string longer than 255 characters. You can get around this by passing an array (with each member of the array less than 255 characters). Knowing this, I can now write my SQL string as follow (for example):

    Dim strSQL(10) as String

    strSQL(1) = "SELECT "
    strSQL(2) = "table1.field1, table1.field2"
    et cetera

    Then, the CommandText statement is:
    .CommandText = strSQL

    This greatly simplifies my life.

    Another trick, though is to avoid ODBC altogether. Simply use Automation. Add the correct reference (Tools >> References from the VBE menu bar) to your Excel workbook (in my case that is Microsoft DAO 3.51 Object Library).

    With this reference in place, you can define your database like this:

    Set dbs = Workspaces(0).OpenDatabase(dbpath)

    write your SQL string (with no character limitation), and retrieve the results:

    Set rst = dbs.OpenRecordset(strSQL)

    To write the result table to Excel you loop through the fields once to create the headers, then step through each record (and for each record loop through the fields) to write the results to Excel rows and columns.

    The advantage of Automation over ODBC is that Automation is synchronous, while ODBC is asynchronous.

    What?? What I mean by that is ... usually getting the data is only the first step. Right after that we want to process the data. How will you know when the ODBC is finished? Before learning how to use the Automation, I had all kinds of inventive ways to know that the ODBC had finished writing to Excel. With Automation, I have complete control.

    Hope these random thoughts have some value for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1