Using OpenRecordSet on a SQL table linked to Access
-
I have ugraded an Access 97 database to have a SQLserver 2000 backend and a Access 2000 front end with linked tables using a dsnless conection, one of the reports uses the following code
Set mwspCalc = DBEngine.Workspaces(0)
Set mdbCalc = CurrentDb
Set mrstCalc = mdbCalc.OpenRecordset("tabExpectedIncomeCalc", dbOpenDynaset, dbSeeChanges)
Set mdbExWk = CurrentDb
Set mrstExWk = mdbExWk.OpenRecordset("tabExpectedIncomeWk", dbOpenDynaset, dbSeeChanges)
It falls over trying to set up the record sets I have added the dbSeeChanges option so all records should be seen the error I get is "Type Mismatch" I know I am missing somthing simple but I cant think what any pointers would be appreciated.
-
Couple of questions....
Why are you opening this workspace? Set mwspCalc = DBEngine.Workspaces(0)
Why are you opening this database again? Why not just reuse "mdbCalc"?
Set mdbExWk = CurrentDb
Can you modify the SQL tables to put unique indexes on them? Then you should be able to open the tables just by using Set mrstCalc = mdbCalc.OpenRecordset("tabExpectedIncomeCalc")
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Jim, as I said I am upgrading the db I didn't create the original and the opening this was already in the code and as you pointed out is not really required and will probably be removed. the database table "tabExpectedIncomeCalc" already has a unique index as do all the tables, I had to add them wher they were missing when I created the linked tables.
I have tried your suggestion Set mrstCalc = mdbCalc.OpenRecordset("tabExpectedIncomeCalc") but still get the same message "Type Missmatch"
Dave Wx
-
what have you used as declarations for the recordset objects?
for example
Set mrstCalc = mdbCalc.OpenRecordset("tabExpectedIncomeCalc")
are you explicitly specifying ADO recordsets?
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply