October 8, 2009 at 12:55 am
I sorted out the Native client driver and SQL authentication and performance is MUCH better. I may be kidding myself as in my test environment I am the only user, but reports and forms run quite well. I struck a small problem (I think) with some code that I have recently added to the VBA.
I thinks this is refered to as DAO, but the code is this:
Set MyDB = CurrentDb
Set RunDetailTbl = MyDB.TableDefs![tblRosterRunsDetail]
Set RunDetailSet = RunDetailTbl.OpenRecordset(dbOpenTable)
The last line generates an "Invalid Operation" error.
Can someone point me in the right direction, cheers and thanks again.
October 8, 2009 at 6:41 am
Good to hear that things are working better.
It appears that you want to open the entire table in a recordset. Not sure exactly what you need to do, but here are two versions:
Dim MyDB As DAO.Database
Dim RunDetailSet As DAO.Recordset
'
Set MyDB = CurrentDb
Set RunDetailSet = MyDB.OpenRecordset("tblRosterRunsDetail", dbOpenDynaset, dbSeeChanges)
Dim MyDB As DAO.Database
Dim RunDetailSet As DAO.Recordset
Dim RunDetailTbl As String
'
Set MyDB = CurrentDb
RunDetailTbl = "tblRosterRunsDetail"
Set RunDetailSet = MyDB.OpenRecordset(RunDetailTbl, dbOpenDynaset, dbSeeChanges)
October 8, 2009 at 2:44 pm
sorry, but this is a case of fix one problem and move on to the next. Hope you dont mind
The next line that fails is :
CurrentDb.Execute "UPDATE tblRosterRunsDetail SET tblRosterRunsDetail.Start_Time = #" & dtStart & "# WHERE tblRosterRunsDetail.ID = " & intCurrentVID
This generates run time error 3622: "You must use the dbSeeChanges option with OpenRecordset when accessing a sql server table that has an IDENTITY column"
October 8, 2009 at 3:08 pm
Instead of "CurrentDb.Execute " try "DoCmd.RunSQL "
October 9, 2009 at 7:55 am
If all you are doing is running that UPDATE statement then you don't need all that previous code to open the recordset.
October 9, 2009 at 3:27 pm
Thanks WIlliam, the DoCMD did the trick. I will try it without the other commands to see if it still works. BTW, really appreciate all your efforts on this one. I was ready to abandon SQl Server, but now I have a shot at it. However, I feel if I leave my front end as Access mdb then I wont really see any benefits. My next step will be to try and convert my mdb front end to adp. The front end has literally hundreds of queries, reports and forms. It would not be viable to rewrite all that stuff from scratch so I'm hoping there is a way forward with adp or perhaps something different???
cheers, Alan
October 10, 2009 at 6:40 am
Early this year we converted an Access MDB to use the MDB front-end / SQL database concept just as you are doing. The app has about 100 tables, 1300 queries, 200 forms & 200 reports.
After the initial conversion we did extensive side-by-side testing, opening every form, running every report, clicking every button etc. to verify that both versions gave the same results. We then identified bottlenecks in the MDB / SQL version & tackled them one-by-one.
One bottleneck was that some Access queries were inefficent. Those were re-written for better performance. Many were the kind of "query on top of a query on top of a query" that is easy to do in Access but worked poorly with the SQL db. Another query problem involved what I call a "hidden inner join" where you have a left join but the outer table has criteria being applied, which effectively makes it an inner join; changing to use an inner join made those much faster (in both versions).
Some reports were very slow due to the volume of data being pulled. For those, we used pass-thru queries using either stored procedures or T-SQL. We also learned that when you preview a report, Access runs the underlying queries (of course) but then if you go ahead & print that report, Access will run the queries all over again; so some large reports would preview OK but then failed (error 3146 ODBC connection failed) when being printed; had to provide the user separate buttons for preview & print to handle that.
I have not worked with ADP's but they are very different from MDB's and you could be looking at a lot of work converting to that format.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply