Results 1 to 9 of 9

Thread: DAO Problem

  1. #1
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Unanswered: DAO Problem

    Hi Folks,

    Having a problem running some DAO in Excel 97.

    Here is the code :

    ''Function to set a DAO recordset from anywhere in the project
    Public Function SetRecordset() As Boolean

    Dim ws As Workspace
    Dim db As Database
    Dim rs As Recordset
    Dim strConnection As String
    Dim strSQL As String

    Set ws = DBEngine.Workspaces(0)

    strConnection = "ODBC;DSN=MYDATA"

    Set db = ws.OpenDatabase("", False, False, strConnection)

    Set rs = db.OpenRecordset("select * from tblData")

    ws.Close

    End Function


    I know I'm connecting ok but I keep getting type mismatch errors and can't seem to tell why.

    Cheers

  2. #2
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi,

    On which line does the code stops?

    Kind regards,
    Dennis
    Kind regards,
    Dennis

  3. #3
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Set rs = db.OpenRecordset("select * from tblData")


    Has a type mismatch error ???

  4. #4
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi,

    OK, let see if the following approach may help You:

    Code:
    Sub SetRecordset()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    strSQL = "SELECT * FROM tblData"
    
    'Change the path and name to Your own database.
    Set db = DBEngine.Workspaces(0).OpenDatabase("e:\Arbetsmaterial\XLData1.mdb")
    Set rs = db.OpenRecordset(strSQL)
    
    MsgBox rs.RecordCount
    
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    End Sub
    Kind regards,
    Dennis
    Kind regards,
    Dennis

  5. #5
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Thanks for your help Dennis

    Need the code to access a SQL Server database not a access

    DSN=MYDATA is an sytem DSN that ODBC's a SQL Server instance

    any ideas ?

  6. #6
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi,

    OK, I do not have access to an SQL db on a server so I set up a local System DSN via the ODBC-manager.

    Following seems to work OK and I wonder if You have the error in the SQL-statement, i e does the table 'tblData' exist?

    Code:
    Sub SetRecordSet1()
    Dim ws As Workspace
    Dim db As Database
    Dim rs As Recordset
    Dim strConnection As String
    Dim strSQL As String
    
    Set ws = DBEngine.Workspaces(0)
    
    strConnection = "ODBC;DSN=LocalServer"
    strSQL = "SELECT * FROM Customers"
    Set db = ws.OpenDatabase("", False, False, strConnection)
    Set rs = db.OpenRecordset(strSQL)
    
    MsgBox rs.RecordCount
    
    ws.Close
    
    End Sub
    Kind regards,
    Dennis
    Kind regards,
    Dennis

  7. #7
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Thanks Dennis,

    Managed to bring myself into the 21st Centuary and got ADO working.

    Cheers Again

  8. #8
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi,

    Good news especially that You use ADO

    Kind regards,
    Dennis
    Kind regards,
    Dennis

  9. #9
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Red face

    Had a module before that was using Excel 2000 and ado to connect to SQL Server. Was not sure if that would work using excel 97 so thought I revert back to DAO which I was sure I could use.

    Pain in the arse.

    Tried ADO - Nay bother, as they say in Scotland

    All the best big man

Posting Permissions

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