Results 1 to 6 of 6
  1. #1
    Join Date
    May 2014
    Posts
    16

    Unanswered: Access Problem Reading SQL Server Database

    I have an Access Database program as a front end and a SQL
    Server database as a back end. The program uses VBA code as well.
    The VBA code in the program needs to read a SQL record
    where three fields must be matched. These fields are

    Buyer varchar(50)
    DateAdded smalldate
    PartNumber varchar(50)

    I am rather new to this and cannot get the DateAdded to
    work. What help can be provided will be appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what are you using? its kind of hard to help when you describe a problem in a fairly abstract way without providing the actual code that is (or isn't) doing the dirty deed

    depending on where you are based you may have to format the date to the appropriate NLS settings.

    there is a long standing bug in SQL server to do with dates... IIRC its to do with a date in a PK, as the bug relates to (virtually) RoW format and you are North American based I doubt thats the cause of the problem

    to get round date issues when coding I normally send literal values as ISO format (yyyy/mm/dd), so code will nearly always work irrespective of the localisation of the PC.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Generally speaking, you can read one or several rows from a table located in a database on a SQL Server like this:
    Code:
    Function ReadRows(ByVal ConnectionString As String, ByVal SQLString As String) As Variant
    
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim lngRowCount As Long
        
        Set qdf = CurrentDb.CreateQueryDef("")
        With qdf
            .Connect = ConnectionString
            .SQL = SQLString
            Set rst = .OpenRecordset
            If Not rst.EOF Then
                With rst
                    .MoveLast
                    lngRowCount = .RecordCount
                    .MoveFirst
                    ReadRows = .GetRows(lngRowCount)
                    .Close
                End With
            End If
        End With
        Set rst = Nothing
        Set qdf = Nothing
        
    End Function
    Which you can use:
    Code:
    Sub TestReadRows()
    
        Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=ISKENDER;DATABASE=Sales;Trusted_Connection=Yes;"
        Const c_SQL = "SELECT * FROM Tbl_OrderLines WHERE Order_Number = 22089;"
        
        Dim var As Variant
        
        var = ReadRows(c_Connect, c_SQL)
    
    End Sub
    If dates are involved, follow Healdem's advice ans use:
    Code:
    Sub TestReadRows()
    
        Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=ISKENDER;DATABASE=Sales;Trusted_Connection=Yes;"
        Const c_SQL = "SELECT * FROM Tbl_OrderLines WHERE Date_Customer_P_O_Number = '2006-04-28';
    
        
        Dim var As Variant
        
        var = ReadRows(c_Connect, c_SQL)
    
    End Sub
    Have a nice day!

  4. #4
    Join Date
    May 2014
    Posts
    16
    Sorry; I do not know some of your terminology. NLS, IIRC, ISO etc. I did say "I am rather new to this...". Thanks anyway. :\

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    NLS: National Language Support
    IIRC: If I remember correctly
    ISO: International Standards Organisation
    PC: persoanl Computer
    PK: Primary Key
    SQL: Structure Query Language
    Row: Rest of the World
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2014
    Posts
    16
    You did misspell personal (PC: persoanl computer) or is there another kind of computer I am not aware of? (@_@)

Tags for this Thread

Posting Permissions

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