Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    17

    Unanswered: Insert form data into SQL statement, VBA, ADO

    How do I embed VBA form inputs into SQL syntax using an ADO conn. querying an Access database?

    I envison it to look like this:

    SELECT *
    FROM tblCustomer
    WHERE cmbCustomer = tblCustomer.Company AND lstContacts = tblCustomer.ContactName;

    but it doesn't work.

    In other words, how do I make these SQL compatible?

    lstContacts, cmbCustomer, txtAddress,...etc


    Thanks,
    rizzo89

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    ref this ADO Tutorial.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    May 2006
    Posts
    17

    Good tutorial but, didn't help.....

    Do you know of any more advanced tutorials?

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Do you know of any more advanced tutorials?
    From what I saw, you needed a basic tutorial, not a more advanced one...

    When you build the string that you are using, the string must contain the VALUES of your form objects/controls - NOT their names.

    Also, you have your where clauses backwards. They should have the field name to the LEFT of the operator, and the field value to the right.

    Also, text fields must use apostrophes as delimiters. Dates (in Access only) must use hash characters (#) as delimiters. (All other databases use ANSI standard SQL syntax (the apostrophe) for date dselimiters as well.) Numeric fields must use no delimiters.

    Example:
    Code:
    WHERE tblCustomer.Company = ' & cmbCustomer & "'"
    You will need to define and instanciate an ADO recordset, populate the recordset (using your SQL statement,) and copy the field values from the recordset to the controls on your form.

    Again, take a look at the tutorial I linked above.
    Last edited by loquin; 10-10-06 at 14:04.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    May 2006
    Posts
    17

    I really appreciate your help here.

    Code:
        Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Set conn = New ADODB.Connection
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\Contacts.mdb"
        conn.Open
        
        Dim strContactName, strCompany, strPhone, strFax, strEmail, strAddress1, _
        strCity, strState, strZIP As String
        
        Set strContactName = frmNewContact.txtContactName
        Set strCompany = frmNewContact.txtCompany
        Set strPhone = frmNewContact.txtPhone
        Set strFax = frmNewContact.txtFax
        Set strEmail = frmNewContact.txtEmail
        Set strAddress1 = frmNewContact.txtAddress1
        Set strCity = frmNewContact.txtCity
        Set strState = frmNewContact.txtState
        Set strZIP = frmNewContact.txtZIP
               
        ' Create new record.
        Set rs = conn.Execute( _
            "INSERT INTO tblCustomer(ContactName, Company, Phone, Fax, Email, Address1, City, State, ZIP)VALUES(strContactName, strCompany, strPhone, strFax, strEmail, strAddress1, strCity, strState, strZIP)")
        
        ' Close the database.
        rs.Close
        Set rs = Nothing
        conn.Close
        Set conn = Nothing
    What am I doing wrong here?

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by rizzo89
    Code:
        Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Set conn = New ADODB.Connection
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\Contacts.mdb"
        conn.Open
        
        Dim strContactName, strCompany, strPhone, strFax, strEmail, strAddress1, _
        strCity, strState, strZIP As String
        
        Set strContactName = frmNewContact.txtContactName
        Set strCompany = frmNewContact.txtCompany
        Set strPhone = frmNewContact.txtPhone
        Set strFax = frmNewContact.txtFax
        Set strEmail = frmNewContact.txtEmail
        Set strAddress1 = frmNewContact.txtAddress1
        Set strCity = frmNewContact.txtCity
        Set strState = frmNewContact.txtState
        Set strZIP = frmNewContact.txtZIP
               
        ' Create new record.
        Set rs = conn.Execute( _
            "INSERT INTO tblCustomer(ContactName, Company, Phone, Fax, Email, Address1, City, State, ZIP)VALUES(strContactName, strCompany, strPhone, strFax, strEmail, strAddress1, strCity, strState, strZIP)")
        
        ' Close the database.
        rs.Close
        Set rs = Nothing
        conn.Close
        Set conn = Nothing
    What am I doing wrong here?
    First, get rid of the extraneous SETs that I highlighted in red. SET is only used in conjunction with instanciating an object - not in variable assignment.

    Second; if you are inserting a record, which will NOT return any data, why are you using a recordset?

    Just use the command.Execute method to send the SQL command directly to the database.

    Code:
    cn.Execute "Insert into ..."
    Next, it is a good idea to build a sql string so that you can look at it in the debug mode, instead of building it.


    Code:
    Dim strSQL as String
    strsql =  "INSERT INTO tblCustomer(ContactName, Company, Phone, Fax, Email, Address1, City, State, ZIP)VALUES(strContactName, strCompany, strPhone, strFax, strEmail, strAddress1, strCity, strState, strZIP)"
    
    debug.Print strSQL
    
    conn.Execute strSQL
    Now, if you add a breakpoint to pause execution at this point (at the conn.execute line), you can view the sql string contents in the immediate window to see what the problem is.

    Again, I say that you need to build a string that contains the CONTENTS of the variables, and NOT the variable name itself.

    For example, to insert a single text value into a field named fldText ot table tblTest, your sql string would have to look like:
    Code:
    INSERT INTO tblTest (fldText) Values ('THIS IS THE TEXT YOU WISH TO ENTER...')
    In order to BUILD and execute a string like this, using internal variables, you code would look something like
    Code:
    strText = "THIS IS THE TEXT YOU WISH TO ENTER..."
    strSQL = "INSERT INTO tblTest (fldText) Values ('" & strText & "')"
    conn.execute strSQL[/code]Do you see how you have to BUILD a string? If you have the variable names inside quote marks, VB thinks that the variable NAME is part of the string! Not the variable contents. Also note that you have to include apostrophe field delimiters around text field data, and (ONLY for Access databases, as access uses non standard SQL) the hash mark (#) around DATE field type data. Numeric field types must have NO delimiters.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    May 2006
    Posts
    17

    Thank You!

    This helps tremendously...I am going to spend some time with this info.
    I can actually add a new contact without a pastespecial to an excel sheet!
    Once I get this souped up I have one other problem...
    Currently I am opening a spreadsheet with a list of contacts and copying the whole list into my program. I have a combobox that selects from a unique company name list and then references a unique contact list. Then filters out the contacts into a listbox. The user can type in a company into the combobox and the listbox will show the contacts for that company. Then the contact is selected and the fields are transfered into the excel sheet via an apply button.

    So, right now I can do a SELECT * and bring all contacts in but, the load time to do this is like 10 Seconds. Could you possibly give me some pointers on how to go about doing this? I just want to select contacts, by company and insert info into a custom Excel template.

    Thanks again! You have saved me from alot of frustration.

  8. #8
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    if possible try to maintain the master lists in the for of arrays or something.
    This should improve the performance of your application.
    In GOD we believe. Everything else we Test!

Posting Permissions

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