CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    84

    Help with Access DB and DataGrid

    Hi

    SInce this is my first work with Access DB and the Datagrid, i would need a little help. I have already built my Access DB File that contains 1 table called "Table1" and that table contains 4 fields.

    in my VB code:

    How do i write to my Access DB using an ADODB Recordset?
    How do i read a specific cell on a specific row of my DB?
    How do i clear all the data in my VB (just keep the Table and Fields name)?

    thanks in advance

    Martin

  2. #2
    Join Date
    Aug 2003
    Location
    Reading
    Posts
    172
    I don't ever use ado but use dao instead. i dunno which is better (have a feeling it's ado) but I can give you the dao 3.6 answers:

    First, you need to specify a recordset. This can be called by an SQL query or by giving the whole table name (also SQL but not really a query).

    Then, you can move around records and call field values. You can also delete, amend and apend using SQL or the vb recordset. here are the code egs:
    Code:
    Dim rsMyRecordset as recordset 'may need to be dao.recordset if your using both dao and ado.
    Dim lngRecordcount as long
    Dim x as long
    
    set rsMyRecordset = currentdb.openrecordset("Table_Name")
    
    rsMyRecordset.Movelast 'need to do this before recordcount for some reason
    lngRecordcount = rsMyRecordset.recordcount
    rsmyrecordset.movefirst 'you can move first before doing the recordcount if you need to
    for x = 0 to lngRecordcount
    
        'Do something here if you like and use rs.movenext/rs.moveprevious/rs.move x
    
    next 'x
    'or, even easier way to do the above:
    rsMyrecordset.MoveFirst
    while not rsMyrecordset.EOF 'EOF=End Of File.  Means until the end of the records
        'do something here.
        rsMyrecordset.edit
        rsMyRecordset!MR_MYField = x
        rsMyRecordset.update
    wend
    'add a new record:
    rsMyrecordset.addnew
    'Fill in your field values as above
    rsmyrecordset.update
    
    'That should get you started with the VB method of editting a recordset.  Heres a quick look at the SQL method.
    
    Dim strSQL as string
    
    strSQL = "DELETE Table_Name.MR_MyField FROM TableName WHERE Table_Name.ID = 4;"
    
    docmd.runsql(strsql) 'I find it easier to use a string var but you can just type the query in here instead.
    
    'You can create your queries using the Access Query builder then copy the SQL into your code.
    That should get you started anyway. Always look at the Help if I have used a term that you don't recognise or understand. If you get stuck then repost

    NB1: For SELECT queries alway use a recordset coz a SELECT in the DoCmd.RunSQL will not do anything but tie up resources for the time to do the query.
    Equally, you cannot use DELETE, INSERT etc in a recordset coz this is a command and not a search.

    NB2: the table/field seperater is '.' or full stop/period where as the VB recordset/field seperater is '!' or exclamation point. (eg SQL = "SELECT MYTABLE.MYFIELD" and VB = MYRECORDSET!MYFIELD)

    -edit- To select everything into a recordset use '.*' in the SQL and to delete everything use a DoCmd with "DELETE TABLE_NAME.* FROM TABLE_NAME;". That will delete everything. "KILL TABLE_NAME" deletes the table


    Hope that helps
    Greyson
    Last edited by Greyson; August 9th, 2004 at 10:37 AM.

  3. #3
    Join Date
    Jul 2004
    Posts
    84
    Hello Greyson

    Thanks ver much for the reply!

    Got a question: Do i need to use a "Jet connection string" in my code?
    That is something that is not clear to me.

  4. #4
    Join Date
    Aug 2003
    Location
    Reading
    Posts
    172
    Long answer:
    if the tables are already in your access database or are already linked to the access database (in tables view with a black arrow in one corner) then there is already a JET connection in place. If you need to connect to, or link another datasource then you will need to connect to it with a connection string or JET connection

    Short answer:
    If you can see the table in the table view then no.

    Exceptions:
    If you have linked into a datasource that requires a password and didn't check the 'remember password' box when you linked it, then you will either need to use a connection string anyway or just delete the link and relink but remember the 'remember password' option. this option is a pit obscure but it is in there somewhere

    I'm off for the day but I or anyone else who reads this will be happy to answer any more questions on Jet

  5. #5
    Join Date
    Jan 2004
    Location
    TX, USA
    Posts
    347
    I recently came by a basic article on ADO and Access that seems fairly well commented.


    http://www.ftponline.com/archives/pr...007/gs0007.asp
    Cathy
    Jan 2004 - NEWBIE to VB6
    Any and all help appreciated

  6. #6
    Join Date
    Jul 2004
    Posts
    84
    Cathy

    This is great stuff!


    Thanks for sharing


    Martin

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured