Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    ADO Add Record (VBA 6)

    I'm using VBA to add a record to a data table which I have successfully created. I've been successful with adding a record... until I attempt to do so with a function and variables.

    The .Execute line, below, works well. I'm having trouble when I use a variable instead of the literal string enclosed in single quotes. The code which fails is below, commented out. I've tried using a variable for the comma (x = Chr$(44)) as well as $#44.

    What must I do? Thanks.

    .Execute ("INSERT INTO tblQuick([OneUniqueID], [Two], [Three], [Four], [Five], [Six]) " & _
    "VALUES('one', 'two', 'three', 'four', 'five', 'six' );")


    ' .Execute ("INSERT INTO tblQuick([OneUniqueID], [Two], [Three], [Four], [Five], [Six]) " & _
    ' "VALUES(" & strQLName & "," & strTo & "," & strCC & "," & strBCC & "," & strRe & "," & strSalutation & ");")

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ADO Add Record (VBA 6)

    You are missing the apostrophes:
    <pre>Execute ("INSERT INTO tblQuick([OneUniqueID], [Two], [Three], [Four], [Five], [Six]) " & _
    "VALUES('" & strQLName & "','" & strTo & "','" & strCC & "','" & strBCC & _
    "','" & strRe & "','" & strSalutation & "');")</pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: ADO Add Record (VBA 6)

    I understand now. Thank you!

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: ADO Add Record (VBA 6)

    May I ask another question? (I'm new to ADO.) I'd like to create a record set. My code doesn't raise an error, but the record count is -1, which tells me something is wrong. There are 16 records in the table, about half of which have Richard in the name field. I assume my problem is with strSQL. Can you offer any advice? Thanks.

    Dim dbConnectStr As String
    Dim conn As ADODB.Connection
    Dim dbPath As String
    Dim strSQL As String
    Dim rs As ADODB.Recordset

    dbPath = "C:richardTestDB.mdb"
    dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"

    strSQL = "Select * from tblQuick where Name = 'Richard' "

    Set conn = New ADODB.Connection

    conn.ConnectionString = dbConnectStr
    conn.Open

    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn
    MsgBox rs.RecordCount

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ADO Add Record (VBA 6)

    Depending on the type of recordset, the RecordCount property can be misleading: -1 means undetermined rather than an error or no records. You may find that if you use rs.movelast and rs.movefirst before accessing the Recordcount, you will get an accurate answer. If you want to know if the recordset is empty, you can test its BOF or EOF property - if either is True when the recordset is opened, then it is empty.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: ADO Add Record (VBA 6)

    Thank you. Both BOF and EOF were False. rs.MoveLast raised an Automation Error; rs.MoveFirst did not. I was able to use rs.MoveNext and get the name and value of fields successfully. The recordset is definitely not empty... but do I need to worry about the Automation Error with MoveLast?

    Richard

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO Add Record (VBA 6)

    <P ID="edit" class=small>(Edited by jscher2000 on 07-Sep-08 15:35. I should have included some kind of connection object... I added one.)</P>Different ADO Providers may have different default cursor types. For speed, a forward-only, read-only approach often is preferred. However, if you need to navigate forward and backward, then this is not useful and you need to request a different kind of cursor. Compare these examples:

    <code>objRSA.CacheSize = 20
    objRSA.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText</code>

    <code>objRSA.Open strSQL, objConn, adOpenKeyset, adLockOptimistic, adCmdText</code>

    Check out this article for a discussion of the issue: Understanding ADO's Default Cursor Type (Inside Access magazine, May 2001).

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: ADO Add Record (VBA 6)

    Thanks very much. That article provided a good explanation. I scrapped the XML plans and have decided on ADO. I think it's much more efficient, and I'm finding it easier than I expected.

Posting Permissions

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