Access 2003 <=> SQL 2005 Best Practices - Part II (1 Viewer)

If you read a previos post of mine, I am working
on an application, trying to move the back-end part to an SQL Database.

What I am starting from, is a "classic" Access 2003 application splitted in 2 parts:
the front end with the application consisting of "fixed data" tables, queries, forms, reports and modules
and a back-end with just the "core tables" with user data.
Until now things have worked fine, using a central location for the back-end .mdb
and a dozen of users accessing it with their own copy of the front-end .mdb
A copy of the same back-end structure database (but empty) is also stored
on each client, so the users can work also if it is not connected to the network,
just attaching the "local copy" of the database.
This is good also when the client goes out on a job trip.

Due to a growth of the back-end and to the number of concurrent user,
I am considering to move the back-end to an SQL server (actually the 2005 version).
I already used the "Microsoft SQL Server Migration Assistant for Access"
successfully, and transfered the back-end data to an SQL Server 2005.
I then changed the links of the front-end and now I am testing
the goods and the bads of this switch.
I must say that the result is not bad: the application works well
on nearly all the situations, but it's not flawless as I wanted.

As work progress I am facing new problems, some of which
can be solved with a light change of approach about the way
you manage data (change your query style)
and some of them, just make you think there is something wrong from the base....
E.G.: It seems impossible to have a memo field
working through the linked table method, if it has more than 255 chars.
Another thing I am experiencing is that if you do some single operations,
you can get the job done, but if you do them in sequence,
you can get a "concurrent action" error.

I changed and tried a couple of version of the ODBC driver,
but this problem doesn't seem to have a solution.
Searching inside this foum I found that the only way
to have a good and smooth working link with an SQL server,
is to use the Access Project (.adp),
so I begun to read and document about the way to lay down a project of this kind.
I could give it a try by "converting" all the object from the current .mdb front-end,
but some unanswered questions just leave me with doubts.

Now the questions:
Is it possible to maintain a front-end / back-end configuration using .adp?
More precisely: is it possible to manage a connection
that can switch between SQL server AND a .mdb database?

If I completely convert the frontend, I will have to transfer also the "common tables"
and re-create all the queries.
I suppose the queries will be created on the SQL server after this operation.
And after the "conversion", I will have to distribute the .adp instead of the .mdb
But what about the local copy of the back-end database (used to work off-line)?
I am thinking something like installing the SQL server on each client...
please, tell me this is NOT the way!
Any help will be appreciated.

I would like to address some of your points, and there are many on here who can give you advice on the other points. Your Environment seems very similar to mine. I converted an Access 2003 Database to an SQL Server 2005 Database, and had similar issues. I also used the "Microsoft SQL Server Migration Assistant for Access" to transfer the back-end data to an SQL Server 2005.

You are having problems with a memo field that has more than 255 chars. Check the SQL Server Definition of the field. A Memo Field should not be defined as nvarchar(255). Instead it should be nvarchar(MAX). In my applications this is currently good for fields having over 4000 characters, but I am sure the limit is higher than that.

Another thing I am experiencing is that if you do some single operations,
you can get the job done, but if you do them in sequence,
you can get a "concurrent action" error.

Although an Access Project (.adp) is probably the best way to do what you want, it is not the only way. Recreating ALL of my queries would have been a lot of work, so I converted about 40% of the queries to SQL Server Views, and left the rest in MS Access 2003. The formula was simple. If SQL Server could run it WITHOUT Change, it was moved. If not is stayed behind. After the initial load, only three or four additional queries had to be converted.

SQL Server only needs to be installed ONCE, on the system that houses the Database. My other users all have ODBC Connections to that device.

Good luck with your project!

Hello, and thanks for the reply.
I checked the definition, and the field was already correctly translated as nvarchar(max) by the migration assistant.
The problem comes out also if I simply open the linked table using the Access tables view, and I try to insert more than 255 digits in the field...
It comes out with the "write conflict" dialog box, with the three "save record", "copy to clipboard" and "drop changes" buttons, with the first button disabled.
Any other input or change with fewer digits in the same field, is accepted without errors.

My application is extremely modular, so I use the same procedures and queries
in many parts: sometimes, just because I do a sequence of deletion and insertion,
i get this enigmatic 3146 "ODBC--call failed" error.
In other parts, doing "nearly" the same things, everithing goes well.
I am working on it, and try to understand what triggers this error,
but suggestions from experienced user sure will help. ;-)

OK, I tried many things right now, to get the Memo Field working, and I came across various things.
I tried to play with data type in SQL server between vnarchar(max) and ntext,
and I get the same problems (so the data type is not the problem):
I open the linked table (from the access table view) and tried to manually input some data: apparently without any logic, some data can be edited and some just cannot (i get the "write conflict" error dialog, with three buttons "save record", "copy to clipboard", "drop changes" - with the first button grayed out [aka disabled]).
I have rows with the memo field empty that can be edited, and some not.
I have rows with the memo field with some data (less than 255 char) that can be edited, and some not.
The rows that already have the memo field with more than 255 char just cannot be edited.
In any of the above situations, if I do not touch the memo field, I can change all the data I want.

When I insert a new row, I can freely insert any data, also more than 255 char.
If I edit this record in a secod time, I can always make changes, also more than 255 char without getting the error.
Maybe some "repair" or "normalization" operation should be done in the SQL database?

Inside my application, if I bound the form on the table, I get or I do not get the same errors for the above situations.

One more thing: thinking there was some problem with direct input, I built a vb function that simply uses an update query to set the value of the memo field of this table: the function works as long as the text passed to update the field is <= 255 chars; no matter if I define all variables as variant, and the parameter as Memo.
No matter if the record is new or old, because the error this time is about "Run-time error '3271': Invalid property value": the query parameter just won't accept a string that long.

Any suggestions?

I am seriously considering to replace the Memo field with a set of 255 chars fields...
Until now, the only way to make it work is:
1) insert a new record (I must open a recordset in VB code, just because an update query will never work)
2) "move" (change indexes to) the related records in the "child" tables
(good luck I only have 3 child tables!)
3) delete the original record from the master table
4) finally rename the ID of the new master record to match the one I deleted...
all this just to update a Memo field!!
Any suggestions?

By the way: I think this issue about being unable to update a memo field (with more than 255 chars) with a parameter update query is an Access bug.
It does not work either in a plain Access environment!
And it should work because in Access you can explicitly define a parameter as Memo type.