Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: VB & Access basics

    I recently learned I needed to learn to use MS access 2002. I have been programming databases in php (mysql mostly) for many years.

    I have a good knowledge of SQL, but no Idea how to use it in Access.

    I can get a form created and can figure out how to program it (using events and such). What I can't do is figure out how to connect to and query the database. I'm trying to figure out how I interact with the tables through the Access VBA interface.

    How do I form a connection to the database? How do I send a query and how is the data returned (in some sort of object I would guess)? How can I insert data into a table?

    Like I said, I know SQL and databases, but I have no idea how to get at it in Access.

    If there is a good online tutorial for Access 2002 I would appreciate it, I would buy a book but I'm poor

    Thanks
    TG

  2. #2
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59

    Places to start

    I have only a moment to reply so I can't give you examples or anything at the moment.

    In Access help, lookup Recordset Objects.

    You'll have to declare a Database Object and at the very least, a Recordset Object. Then you can query the database. The results are returned in the recordset that you have initialized. There are also QueryDef objects. I'm rusty on my Access and I never used them much. As far as I recall, I used them to run existing queries that I created in Access or to create new ones. That's as opposed to having my code execute a SQL statement on the fly and then have it go away.

    Here's a general picture, YMMV.

    Dim db As Database
    Dim rs As Recordset

    Set db = CurrentDB
    Set rs = db.OpenRecordset(yada,yada,)

    With rs.
    do stuff with your data in here
    .Close
    End With

    I often declare a string (strSQL) for my SQL statement and then use it in my OpenRecordset to do whatever it is that I want. The string variable goes with other stuff you may want to specify in the "yada, yada" section.

    There are lots of different ways to do this - each, I suppose, for a different reason. But looking up those keywords is a good place to start.

    There are instructions in Help providing syntax for connecting to an external data source as well.

    Good luck! If you want more specific instructions for your situation, let me know what kind of stuff you are trying to do. I may be able to post a more detailed example.

    Magee
    Last edited by mageem; 01-30-04 at 21:08.

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    Thanks!

    I am getting an error. I'm pretty sure I need to include a header file or library that contains the definitions like "Database" and "Recordset" (I get type doesn't exist errors).

    On another note, when designing a form - how can I get rid of the section on the bottom with the "Record:" [arrows] [number field] [arrows]? I'm not even sure what it is, but It doesn't appear to serve any purpose so I would like to get rid of it

    Right now I am just learning access, I hope to eventually build a parts tracking database.

    One more thing. Will an 2002 Access db file be compatible with Access 2000? Backwards combatability is somewhat imporant to me, if it's possible I want to shoot for it.

    Sorry for all the questions, I appreciate the help.

    Thanks
    TG
    Last edited by TG121; 01-30-04 at 22:34.

  4. #4
    Join Date
    Aug 2003
    Posts
    47
    Heya.

    Here's how you interface through VB to your tables:

    1) You just want to get a value of some field Field1 from Table1 with index value held in *variable*
    Use DLookup function like this:

    DLookup("[Field1]", "[Table1]", "[*index*]=" & *variable*)
    *variable* can be a value of a control on a form, VB variable, etc.

    2) You want to run an SQL query on Table1 do the following:

    Dim strSQLquery As String

    strSQLquery = "SELECT ... your usual query stuff"
    DoCmd.RunSQL strSQLquery

    You can also incorporate VB variables in your SQL strings as follows:

    strSQLquery = "SELECT * FROM Table1 WHERE Index=" & *variable* & "AND [Request ID]=" & *variable1*

    This should getcha started. I also figured out a lot of neat Access tricks from just reading help files.
    F..ck it, gimme a beer... Jimi Hendrix RULES!

  5. #5
    Join Date
    Aug 2003
    Posts
    47
    Ooops!

    I forgot that you can olny run action queries from VB:

    UPDATE SET ...
    INSERT INTO ...
    APPEND ...
    DELETE ...

    Use DLookup function to simulate SELECT ...
    Dlookup("[Field Name]", "[Table Name]", "[*WHERE condition*]")
    F..ck it, gimme a beer... Jimi Hendrix RULES!

  6. #6
    Join Date
    Jan 2004
    Posts
    4
    Set rst = db.OpenRecordset("SELECT * FROM ComponentType", dbOpenDynamic)

    Produces an error- it works fine without the ', dbOpenDynamic'. How am I supposed to open a recordset so I can write to it / update rows in the database?

    And I'm still wondering how I get rid of the strange footer in the forms.

    Thanks

    TG
    Last edited by TG121; 01-31-04 at 18:02.

  7. #7
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59
    Instead of this:

    Set rst = db.OpenRecordset("SELECT * FROM ComponentType", dbOpenDynamic)

    Try substituting "dbOpenDynaset" for "dbOpenDynamic." I'm on a Mac so I can't fire up Access so I'm working from memory but I don't recognize dbOpenDynamic as an option.

    That should permit you to do any kind of DDL statement in your tables.

    As far as the form is concerned, open your form's properties. Under Formatting options, look for something called "Navigation bar or buttons. I don't think it's Record Selectors because I'm pretty sure those are the vertical bars to the left of your form." Again, I'm away from a PC.

    Regardless, that's where you'll find the option for the record navigation buttons at the bottom of the form. Change the "yes" to a "no" and you're good to go. If I remembered the name incorrectly, just try turning off different "show yada, yada button/bar" options on that tab and see what happens. You'll find it.

    As far as the header or library is concerned, there's a vb .dll file that you should have specified for use by Access. I can't recall where it is that you specify it. I think it's under the Tools menu. There's a list of DLLs and you just check the ones you want included. I'm surprised though that it doesn't recognize those data types. If you did a full install then that DLL should have been linked automatically, I would have thought.

    Magee
    Last edited by mageem; 01-31-04 at 21:39.

  8. #8
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59

    Found it, I think!

    From a post that JoeG in this forum sent to another user:

    "Open the database. Do Alt-F11 to go into VBA. Go to tools references on the toolbar. Look for any references that are checked but say missing next to them. You may need to uncheck the missing reference and then scroll down the list and find the proper reference and check that one instead. For example if you are missing 8.3 you might need to scroll down and find 9.0 or 10.0."

    Magee

Posting Permissions

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