DAO Problem

aldo_2003 · #1 (**permalink**) 02-13-04, 11:38

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

Xl-Dennis · #2 (**permalink**) 02-16-04, 21:15

Hi,

On which line does the code stops?

Kind regards,
Dennis

aldo_2003 · #3 (**permalink**) 02-17-04, 05:21

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

Has a type mismatch error ???

Xl-Dennis · #4 (**permalink**) 02-17-04, 06:59

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

aldo_2003 · #5 (**permalink**) 02-17-04, 07:03

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 ?

Xl-Dennis · #6 (**permalink**) 02-17-04, 08:17

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

aldo_2003 · #7 (**permalink**) 02-17-04, 08:18

Thanks Dennis,

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

Cheers Again

Xl-Dennis · #8 (**permalink**) 02-17-04, 08:41

Hi,

Good news especially that You use ADO

Kind regards,
Dennis

aldo_2003 · #9 (**permalink**) 02-17-04, 08:59

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

Internet Services

Web Development

Digital Marketing

Consumer Tech