Upsizing to SQL Server gives 3622 error

[PiedPiper70]

I'm working on upsizing my all Access 2010 front end/back end system to be Access front end and SQL Server 2014 back end. I've immediately encountered the well documented error 3622 having upsized and I have 2 questions I'd like to ask:-

1. Adding the 'dbSeeChanges' parameter is going to be very laborious in a system of this size - is there any way round it?

2. As the upsize is a work in progress, and my day to day work is still in the fully Access system, is it safe to add the above parameter into the exisitng front end code without causing problems?

Thanks
Dave

 

[pbaldy]

1) I don't think so. Find & Replace may be your friend.

2) Yes, it should work with either.

 

[PiedPiper70]

OK. So I need to add dbSeeChanges everywhere there is an OpenRecordset line. I've tried it and it works fine, but I may have 1000 places to make this change. Can anyone suggest a smart way to do this where:-

Most lines are like this: - db.OpenRecordset("tblXYZ")

And others are like this:- db.OpenRecordset("tblXYZ", dbOpendynaset)

And occasionally like this:- db.OpenRecordset("tblXYZ", dbDenyRead)

I can't see how find and replace can work with the first version since the table name changes on every occurence.

Any help gratefully received.
Thanks
Dave

 

[pbaldy]

Offhand I can't think of a way for the first either. It seems unusual to open all the recordsets directly on tables though. I would expect performance problems on larger tables. In any case, I'll think about it and see if a method comes to me.

 

[PiedPiper70]

Firstly, do you think there might be a way to write a function to go through every line in every module looking for a string beginning "openrecordset" and then perform a fairly early string manipulation to add the required text? Can I use vba on the vba ??

Secondly, not sure why you're surprised at my use of opening recordsets directly on tables. In many cases I'm opening them on queries, or sql strings, but I still have a huge number of small and simple functions to, for example, return a person's phone number based on their username etc etc. and I just look them up directly in the table concerned. What's wrong with that?

 

[pbaldy]

Once you've opened the recordset on the table, you must be looping the recordset looking for the specific person. That would be much less efficient than opening the recordset on an SQL statement with a WHERE clause that returned only that specific person's record. You may not notice the difference on a small table, but you would on a larger table. A DLookup() would be simpler and more efficient than looping the table.

I've never done it, but see if this helps:

http://www.cpearson.com/excel/vbe.aspx

 

[PiedPiper70]

Yes, I see what you're saying. In fact, recently, I have been using much more SQL but of course I still have to open a recordset, so the original problem still exists. Looks like I've a sizeable task on my hands.

I will take a look at that link though - thanks for that

 

[Rx_]

shameless plug:
http://www.access-programmers.co.uk/forums/showthread.php?t=223173&highlight=migration+assistant
Instead of the upsizing wizard, this is a free tool that I prefer. So at least there is an option.
Also:
http://www.access-programmers.co.uk...p?t=251208&highlight=sql+server+native+client
The SQL Server Native Client 11.0 is a free ODBC driver that must be downloaded to each client. It is often demo with Azure and such.
It is also another choice since your getting started.
It might be worth you time to look up related DSN-Less connections.
That is a lot to take in, but there for your consideration.

 

[PiedPiper70]

Thanks to all. In the end I did it the hard way by hunting for every line where I opened a recordset, then adding dbSeeChanges. It took me a whole day but I got there in the end and all is well.