ADP link with SQL Server 2005

[ngohil]

G'day everyone.

I hope this is in the right section so here goes.

We have a number of MS Access 2003 database applications at work that we are considering migrating to other technologies.

So at the moment, I am in the process of testing migration options for one of the smaller Access 2003 Database to ADP-SQL Server 2005 setup. The ADP file is - as a number of you will know - a MS Office 2010 file type that holds the forms and reports (all front end elements) and the SQL Server 2005 holds the tables and views (or queries).

The conversion went okay with help of the Upsizing Wizard. When I open the ADP file, I can't see any of the subforms within the main form. So I looked up the data source of one of the subforms and it had an incomplete SQL query. So I copy this from the original .mdb file and paste it in - not expecting it to work the first time, rather trying to see the behaviour when you do this. When I try to run the form again, it throws up the following error:

"The multi-part identifier "frmMainForm.form.txtFromDate.value" could not be found"

Now I tried to look up other posts in this forum to see if I can find anything useful to solve this, but didn't have much luck. I even checked some other forums before I entered the post here without much luck.

Any help would be appreciated. Thank you.

 

[Galaxiom]

SQL RecordSource syntax to refer to an Access object in an ADP is different.

http://support.microsoft.com/kb/278400

 

[ngohil]

So the code

"frmMainForm.txtFromDate.value"

should be more like

"[frmMainForm]![txtFromDate]![value]"

Is that it?

 

[Galaxiom]

Much more than that. The square brackets are just delimiters used in Access where the name of an object has spaces or special characters.

ADP uses parameterised stored procedures held in SQL server. Then the ecordSource with the special syntax feeds the parameter to that procedure. Notice the @ symbol.

However since the upsizing didn't convert the SQL I expect it didn't create the parameterized stored procedure on the server either. I imagine that the upsize converter doesn't handle SQL RecordSources and is only designed to work with stored queries as recordsources but that is just a guess.

In the original Access database try copying the SQL into a query and saving it. Then replace the RecordSource with the name of the new query. Then upsize and see if is handled any better.

Otherwise you will have to get your head around everything on that page I linked. Even if you do get the conversion working you really need to understand the way ADP works if you are going to work with it.

I don't use ADP but had a resonable guess about what was wrong. Enough to find what looks like the right page of advice anyway.

I really only know enough to decide to avoid using ADP. It is too limited because it can only interact with a single database. I prefer to stay with mdb where I can connect to as many data sources as I like even though I have to manage connections and handle the stored procedures manually.

 

[ngohil]

Thanks Galaxiom. I too am of the opinion that query names and not SQL statements work better in the RecordSource property field of forms and reports. The databases in question are legacy systems that I am required to support as part of my role and I would have done them differently if I had the opportunity and time.

I will try this and attempt another migration.

 

[Galaxiom]

I too am of the opinion that query names and not SQL statements work better in the RecordSource property field of forms and reports.

I believe they do work slightly better but only because the execution plan of a stored query is saved.

However I am a big fan of using SQL RecordSources to keep the form or report more self contained and to avoid the clutter of so many queries.

The only reason I suggested the stored query is because I suspect the conversion to ADP might work better that way.

I would just try one and see if it makes any difference. As I said it is really only a guess.