Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Los Angeles
    Posts
    16

    Unanswered: VBA SQL won't execute

    This might belong in a VBA newsgroup, but I think the error I'm getting
    is Access, not VB. I am connecting to an Access database through
    VBA (in Excel) and the "select..." SQL command works fine as long as
    I don't use the qualifier "...where name = 'Paul'. If I try it, I get

    Run-time Error 3201
    Application-defined or Object-defined Error

    Strangely, I can use other qualifiers such as "...where date = #1-1-04#"
    but any qualifier with a string fails. I've tried building the SQL statement
    with double quotes (name = "Paul") using Chr(34) or "" but it still fails.

    If this post is misplaced, my apologies. If you can help, thanks very much...



    Public Sub richdbconnect()

    Dim dbsConn As ADODB.Connection
    Set dbsConn = New ADODB.Connection
    Dim connString As String
    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Rich\JUNK\db2.mdb" & "; Persist Security Info=false"
    dbsConn.Open connString

    '----------- create empty recordset ----------
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    '----------- populate recordset with SQL command
    Dim command As String

    'does not work (with the rst.Open command...)
    'command = "select Table1.name from Table1 where name = 'Paul';"

    'works fine!!!
    command = "select Table1.name from Table1;"

    MsgBox command

    rst.Open command, dbsConn, adOpenKeyset, adLockReadOnly

  2. #2
    Join Date
    Jul 2004
    Posts
    125
    Try by reformatting the Excel column cells in question to text. When undefined, cells are variants and there may be a mistranslation since Excel's natural function is numerals. If you were to import the spreadsheet to Access, you will notice that some field values, which you are assuming to be text, are enclosed in "" [Chr(34)]. The reason for this is that there must be commas in the field and that is how Excel interprets, thinking it's a CSV file. Also, fields that contain numerals, like Zip codes, Access imports these as numbers, thereby loosing the leading zeros for North Eastern states. 2nd solution is to special paste the data into a new spreadsheet as values where the whole spreadsheet was defined as text. Best solution, import the whole spreadsheet and clean it up, defining text as text, date as date, number as number ..... etc. and everything will work fine.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    do you really have fields called "name" and "date"?
    using reserved words for names of things is asking for trouble!

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jul 2004
    Posts
    125
    Thank you izyrider, haven't even noticed that. BTW, in which part of CH is Préverenges?

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here is a Préverenges webcam looking down lac Leman in the direction of Geneva... the town of Morges is in the first bay just off-screen to the right.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jul 2004
    Posts
    125
    Many thanks for revivng old memories. I spent a lot of time on business in the Jura mountains, Bienne, La Chauds de Fonds area.

Posting Permissions

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