Problems accessing SQL Server

[cbender]

I have a rather baffling problem with an Access database application I
am trying to migrate to use a SQL Server backend (via ODBC). The
application is working fine using the Access tables, but when I
connect up to a SQL Server database (2000 or 2005 doesn't seem to
matter) I start getting Application errors (the application just blows
up and Access closes). This did not happen when I initially tested
this back in August 2006, but now even the version I created then
exhibits. I have tried this both on an SP1 and SP2 installation and
am quite baffled. I have also tried importing all of the objects into
a new database but this did not correct the problem. This does not
exhibit the typical behavior of a corrupt Access form that will always
crash for instance when you click a certain button or select a
control. Rather it seems to crash on queries that update the SQL
tables. Also, when I trace into the code to try to isolate the query
it does NOT crash, but then if I just let the procedure run it crashes
every time. Does anyone have any suggestion on how to further isolate
this problem?

Thanks
Cheryl

 

[Sylvain Lafontaine]

What version of Access are you using? Any particularity in your code or in
your tables, for exemple using Bit or BIGINT fields, composite primary keys,
double foreign keys from the same foreign table, any use of
Me.RecordsetClone, any Views, etc.?

Are you able to link to a simple table and a simple form?

 

[cbender]

I think I have solved the problem by adding the option dbSeeChanges to
all queries that update SQL Server tables. The only thing is from
what I understand about this option this really shouldn't matter for
tables that don't have an IDENTITY column should it?

I did find some rather peculiar behavior. There were two queries that
had a dbFailOnError + dbSeeChanges--in both of these the combined
flags cause the application to crash. Once I removed the
dbFailOnError they work fine. Other queries that have both flags set
work fine. So I'm curious what effect the dbFailOnError has on the
ODBC query. Both are parameter queries but the tables they update are
very typical and there is no IDENTITY column on either table.

Some info about my computer/config

Access--2003 SP2 11.6566.8107
SQL Server 2005
ODBC32.dll version 3.525.1117.0 File Version 3.525.1117.0
(xpsp_sp2_rtm.040803-2158)

odbcjt32.dll 4.0.6304.0

Operating System is XP Pro, SP2, (version 5.1 Build 2600.xpsp_sp2_rtm.
040803-2158)

Thanks
Cheryl

 

[James A. Fortune]

I have a rather baffling problem with an Access database application I
am trying to migrate to use a SQL Server backend (via ODBC). The
application is working fine using the Access tables, but when I
connect up to a SQL Server database (2000 or 2005 doesn't seem to
matter) I start getting Application errors (the application just blows
up and Access closes). This did not happen when I initially tested
this back in August 2006, but now even the version I created then
exhibits. I have tried this both on an SP1 and SP2 installation and
am quite baffled. I have also tried importing all of the objects into
a new database but this did not correct the problem. This does not
exhibit the typical behavior of a corrupt Access form that will always
crash for instance when you click a certain button or select a
control. Rather it seems to crash on queries that update the SQL
tables. Also, when I trace into the code to try to isolate the query
it does NOT crash, but then if I just let the procedure run it crashes
every time. Does anyone have any suggestion on how to further isolate
this problem?

Thanks
Cheryl

I haven't used this configuration since 2000, but here is my guess as to
the cause of your problem.

That behavior is typical of timing problems. The reason it runs
properly when debugging is that the debugging process itself gives
everything time to finish. You can set multiple breakpoints and use F5
to run full speed between them as a way of isolating the problem code.
You can also punctuate MilliSleep calls or DoEvents loops/'subroutine
calls' into faulty code, check to make sure it runs correctly now due to
the pauses, then remove them one by one until you find the problem.

For MilliSleep calls, the following declaration can be put in a module,
say modAPIFunctions:

Public Declare Sub MilliSleep Lib "kernel32" Alias "Sleep" (ByVal
dwMilliseconds As Long)

Once that is in place,

Call MilliSleep(1500)

for example, will cause Access to pause for approximately 1.5 seconds.

If pushing F8 every second in debug mode suffices to make the code run
correctly, then one second pauses should be adequate to isolate the
problem. DoEvent loops vary in duration with the OS speed so running a
test with a large loop index should get you close to an accurate pause.
The API function GetTickCount can also help fine tune a DoEvent loop
duration if necessary. The idea is to discover where Access starts a
task and continues on asynchronously. In my experience, this seems to
happen often with data object operations. The fact that you are using
ODBC seems to hint that the data's access time is slowing down over
time, perhaps due to the number of records. Asynchronous operations
that used to complete in time now fail to finish before other code
requiring the result of that operation runs. This usually causes
strange results due to Access doing the best it can with incomplete data
rather than causing a complete blowup but perhaps SQL Server is better
at recognizing that something is wrong and doesn't want to mislead the
user into thinking they have correct results when they don't. The
crashing during update queries also points toward data objects,
especially given the lack of typical corruption behavior. A test with a
small table in place of a large one in the BE, or with a table linked
elsewhere might differentiate the problem further.

As I said, it's been awhile since I have used this configuration so I am
not 100% sure that all this information is correct.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

I think I have solved the problem by adding the option dbSeeChanges to
all queries that update SQL Server tables. The only thing is from
what I understand about this option this really shouldn't matter for
tables that don't have an IDENTITY column should it?

I did find some rather peculiar behavior. There were two queries that
had a dbFailOnError + dbSeeChanges--in both of these the combined
flags cause the application to crash. Once I removed the
dbFailOnError they work fine. Other queries that have both flags set
work fine. So I'm curious what effect the dbFailOnError has on the
ODBC query. Both are parameter queries but the tables they update are
very typical and there is no IDENTITY column on either table.

Some info about my computer/config

Access--2003 SP2 11.6566.8107
SQL Server 2005
ODBC32.dll version 3.525.1117.0 File Version 3.525.1117.0
(xpsp_sp2_rtm.040803-2158)

odbcjt32.dll 4.0.6304.0

Operating System is XP Pro, SP2, (version 5.1 Build 2600.xpsp_sp2_rtm.
040803-2158)

Thanks
Cheryl

Maybe dbFailOnError is about as honest as a Microsoft status bar . I
never tried to update a SQL Server table without an IDENTITY column.
Does putting in IDENTITY columns change the results you discovered?

James A. Fortune
(e-mail address removed)

 

[Sylvain Lafontaine]

You didn't show us any piece of code in your first post, so my understanding
was that you were using bound forms to ODBC linked tables. However, if you
are using VBA code to update the database, then there is not enough
information to give you any advice.

If you are using VBA code to update the database, then you should show us an
exemple of code. You should also tell us if you trying to change data that
are also part of a bound form. You should also tell us if the backend
tables use one or more unique indexes beside the primary key and if the
primary key have or not an identity field.

Finally, you should take a look with the SQL-Server Profiler and see what
happens on the sql server side.

 

[Todos Menos [MSFT]]

you should be using Access Data Projects

MDB is for lamers and retards

 

[cbender]

Thanks James and Sylvain for your suggestions.

I am somewhat baffled about the role of these options and why they
behave as they do and I have not found anything that explains how
these flags work in detail. For two queries I have seen Access crash
when I was tracing through the code (that is the Application Error
occurred almost as soon as I hit the execute line in the code.) When
I removed the dbFailOnError flag the problem seems to have gone away.
This morning I was testing another form that had both flags on both
queries in an update routine. Access crashed on the first query.
Once I remove the dbFailOnError flag it did not crash. I thought
perhaps it was a timing issue as James suggested, but the fact that it
crashes now even when I trace into the code would seem to suggest
otherwise.

Sylvain had asked about architecture. I am using forms bound to
Access temporary tables (the temp tables are in a separate mdb but are
are stored locally for each user) and updating the SQL Server tables
when the user goes to another batch or lot. Some of the routines have
15-20 queries as this is a fairly complex laboratory management
information system (LIMS), while other routines may have only 1-2
queries. Below is an example of how I update SQL Server tables;

'first delete all the reagents for the batch
Set qryTemp =
CurrentDb.QueryDefs("qdelAnalysisBatchReagentsViewS10")
qryTemp.Parameters("AnalysisBatch") = flngAnalysisBatchPrev
qryTemp.Execute dbSeeChanges '+ dbFailOnError
'the query above causes Access to crash when the dbFailOnError
statment is included

'write all the reagents from temp space
Set qryTemp =
CurrentDb.QueryDefs("qappAnalysisBatchReagentsViewS20")
qryTemp.Parameters("AnalysisBatch") = flngAnalysisBatchPrev
qryTemp.Execute dbSeeChanges '+ dbFailOnError

We tend to rely heavily on stored parameter queries. Below is the SQL
for the query above (qdelAnalysisBatchReagentsViewS10)

PARAMETERS AnalysisBatch Long;
DELETE tblALAnalysisBatchReagent.*
FROM tblALAnalysisBatchReagent
WHERE
(((tblALAnalysisBatchReagent.AnalysisBatchNumber)=[AnalysisBatch]));

As far as the IDENTITY columns my keys on tables are almost never
IDENTITY columns and I don't think adding the column to the key is
feasible. I did add a non-primary key column without effect (Access
still crashed)

In summary the changes that make a consistent difference (preventing a
crash) are adding the dbSeeChanges and sometimes removing the
dbFailOnError. Can anyone tell me how to get more detail out of these
crashes? I am not finding the file that Access claims to be sending
with the report (the .crv file does not exist in the directory
mentioned. I find much older files--perhaps this is an SP2 feature).
I downloaded and installed WinDbg but am a ways from being able to use
it with any intelligence.

Thanks again for your help.
Cheryl

 

[Sylvain Lafontaine]

Hum, using QueryDefs to update SQL-Server through linked tables instead of
using passthrough queries or either DAO or ADO objects (recordsets,
connection object) - not sure that you have made the best choice here. Will
all these errors, maybe it's time for you to use a more conventional
approach for updating the database.

The dbFailOnError is used to make the local VBA code to fail when there is
an error message coming from SQL-Server. By default, an error from
SQL-Server is not considered as a local VBA error (the VBA code itself is
perfectly valid). There is also the possibility that the error code from
SQL-Server is just a message or a warning. Maybe displaying the local error
code could put some light on this; however, I don't know if your use of
linked tables + querydef will hide the true error code coming from
SQL-Server. Using the SQL-Server Profiler might help you in understanding
what's happening here. Instead of using the SQL-Server Profiler, you can
also try the ShowPlan option of Access/JET:
http://builder.com.com/5100-6388-5064388-2.html

Make sure that you don't have strange things like nullable Bit fields (often
a problem with Access) or not-nullable fields with no default value
(shouldn't be a problem but in your case, we never know).

Finally, if I were you, I would change my method for updating the backend
database.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thanks James and Sylvain for your suggestions.

I am somewhat baffled about the role of these options and why they
behave as they do and I have not found anything that explains how
these flags work in detail. For two queries I have seen Access crash
when I was tracing through the code (that is the Application Error
occurred almost as soon as I hit the execute line in the code.) When
I removed the dbFailOnError flag the problem seems to have gone away.
This morning I was testing another form that had both flags on both
queries in an update routine. Access crashed on the first query.
Once I remove the dbFailOnError flag it did not crash. I thought
perhaps it was a timing issue as James suggested, but the fact that it
crashes now even when I trace into the code would seem to suggest
otherwise.

Sylvain had asked about architecture. I am using forms bound to
Access temporary tables (the temp tables are in a separate mdb but are
are stored locally for each user) and updating the SQL Server tables
when the user goes to another batch or lot. Some of the routines have
15-20 queries as this is a fairly complex laboratory management
information system (LIMS), while other routines may have only 1-2
queries. Below is an example of how I update SQL Server tables;

'first delete all the reagents for the batch
Set qryTemp =
CurrentDb.QueryDefs("qdelAnalysisBatchReagentsViewS10")
qryTemp.Parameters("AnalysisBatch") = flngAnalysisBatchPrev
qryTemp.Execute dbSeeChanges '+ dbFailOnError
'the query above causes Access to crash when the dbFailOnError
statment is included

'write all the reagents from temp space
Set qryTemp =
CurrentDb.QueryDefs("qappAnalysisBatchReagentsViewS20")
qryTemp.Parameters("AnalysisBatch") = flngAnalysisBatchPrev
qryTemp.Execute dbSeeChanges '+ dbFailOnError

We tend to rely heavily on stored parameter queries. Below is the SQL
for the query above (qdelAnalysisBatchReagentsViewS10)

PARAMETERS AnalysisBatch Long;
DELETE tblALAnalysisBatchReagent.*
FROM tblALAnalysisBatchReagent
WHERE
(((tblALAnalysisBatchReagent.AnalysisBatchNumber)=[AnalysisBatch]));

As far as the IDENTITY columns my keys on tables are almost never
IDENTITY columns and I don't think adding the column to the key is
feasible. I did add a non-primary key column without effect (Access
still crashed)

In summary the changes that make a consistent difference (preventing a
crash) are adding the dbSeeChanges and sometimes removing the
dbFailOnError. Can anyone tell me how to get more detail out of these
crashes? I am not finding the file that Access claims to be sending
with the report (the .crv file does not exist in the directory
mentioned. I find much older files--perhaps this is an SP2 feature).
I downloaded and installed WinDbg but am a ways from being able to use
it with any intelligence.

Thanks again for your help.
Cheryl

Maybe dbFailOnError is about as honest as a Microsoft status bar . I
never tried to update a SQL Server table without an IDENTITY column.
Does putting in IDENTITY columns change the results you discovered?

James A. Fortune
(e-mail address removed)- Hide quoted text -

- Show quoted text -

 

[Sylvain Lafontaine]

Also, if you are updating multiple tables with foreign key relationships,
it's possible that the update of one primary table is not finished when you
try to update/insert the values in a foreign tables. This behavior will
result in a failure from SQL-Server because your VBA code is not necessary
running in synchroneous mode with the SQL-Server.

However, if you run this with the debugger, then you will have no problem
because any request will have ample time to finish before the next request.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hum, using QueryDefs to update SQL-Server through linked tables instead of
using passthrough queries or either DAO or ADO objects (recordsets,
connection object) - not sure that you have made the best choice here.
Will all these errors, maybe it's time for you to use a more conventional
approach for updating the database.

The dbFailOnError is used to make the local VBA code to fail when there is
an error message coming from SQL-Server. By default, an error from
SQL-Server is not considered as a local VBA error (the VBA code itself is
perfectly valid). There is also the possibility that the error code from
SQL-Server is just a message or a warning. Maybe displaying the local
error code could put some light on this; however, I don't know if your use
of linked tables + querydef will hide the true error code coming from
SQL-Server. Using the SQL-Server Profiler might help you in understanding
what's happening here. Instead of using the SQL-Server Profiler, you can
also try the ShowPlan option of Access/JET:
http://builder.com.com/5100-6388-5064388-2.html

Make sure that you don't have strange things like nullable Bit fields
(often a problem with Access) or not-nullable fields with no default value
(shouldn't be a problem but in your case, we never know).

Finally, if I were you, I would change my method for updating the backend
database.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thanks James and Sylvain for your suggestions.

I am somewhat baffled about the role of these options and why they
behave as they do and I have not found anything that explains how
these flags work in detail. For two queries I have seen Access crash
when I was tracing through the code (that is the Application Error
occurred almost as soon as I hit the execute line in the code.) When
I removed the dbFailOnError flag the problem seems to have gone away.
This morning I was testing another form that had both flags on both
queries in an update routine. Access crashed on the first query.
Once I remove the dbFailOnError flag it did not crash. I thought
perhaps it was a timing issue as James suggested, but the fact that it
crashes now even when I trace into the code would seem to suggest
otherwise.

Sylvain had asked about architecture. I am using forms bound to
Access temporary tables (the temp tables are in a separate mdb but are
are stored locally for each user) and updating the SQL Server tables
when the user goes to another batch or lot. Some of the routines have
15-20 queries as this is a fairly complex laboratory management
information system (LIMS), while other routines may have only 1-2
queries. Below is an example of how I update SQL Server tables;

'first delete all the reagents for the batch
Set qryTemp =
CurrentDb.QueryDefs("qdelAnalysisBatchReagentsViewS10")
qryTemp.Parameters("AnalysisBatch") = flngAnalysisBatchPrev
qryTemp.Execute dbSeeChanges '+ dbFailOnError
'the query above causes Access to crash when the dbFailOnError
statment is included

'write all the reagents from temp space
Set qryTemp =
CurrentDb.QueryDefs("qappAnalysisBatchReagentsViewS20")
qryTemp.Parameters("AnalysisBatch") = flngAnalysisBatchPrev
qryTemp.Execute dbSeeChanges '+ dbFailOnError

We tend to rely heavily on stored parameter queries. Below is the SQL
for the query above (qdelAnalysisBatchReagentsViewS10)

PARAMETERS AnalysisBatch Long;
DELETE tblALAnalysisBatchReagent.*
FROM tblALAnalysisBatchReagent
WHERE
(((tblALAnalysisBatchReagent.AnalysisBatchNumber)=[AnalysisBatch]));

As far as the IDENTITY columns my keys on tables are almost never
IDENTITY columns and I don't think adding the column to the key is
feasible. I did add a non-primary key column without effect (Access
still crashed)

In summary the changes that make a consistent difference (preventing a
crash) are adding the dbSeeChanges and sometimes removing the
dbFailOnError. Can anyone tell me how to get more detail out of these
crashes? I am not finding the file that Access claims to be sending
with the report (the .crv file does not exist in the directory
mentioned. I find much older files--perhaps this is an SP2 feature).
I downloaded and installed WinDbg but am a ways from being able to use
it with any intelligence.

Thanks again for your help.
Cheryl

(e-mail address removed) wrote:
I think I have solved the problem by adding the option dbSeeChanges to
all queries that update SQL Server tables. The only thing is from
what I understand about this option this really shouldn't matter for
tables that don't have an IDENTITY column should it?

I did find some rather peculiar behavior. There were two queries that
had a dbFailOnError + dbSeeChanges--in both of these the combined
flags cause the application to crash. Once I removed the
dbFailOnError they work fine. Other queries that have both flags set
work fine. So I'm curious what effect the dbFailOnError has on the
ODBC query. Both are parameter queries but the tables they update are
very typical and there is no IDENTITY column on either table.

Some info about my computer/config

Access--2003 SP2 11.6566.8107
SQL Server 2005
ODBC32.dll version 3.525.1117.0 File Version 3.525.1117.0
(xpsp_sp2_rtm.040803-2158)

odbcjt32.dll 4.0.6304.0

Operating System is XP Pro, SP2, (version 5.1 Build 2600.xpsp_sp2_rtm.
040803-2158)

Thanks
Cheryl

Maybe dbFailOnError is about as honest as a Microsoft status bar . I
never tried to update a SQL Server table without an IDENTITY column.
Does putting in IDENTITY columns change the results you discovered?

James A. Fortune
(e-mail address removed)- Hide quoted text -

- Show quoted text -

 

[Tony Toews [MVP]]

you should be using Access Data Projects

MDB is for lamers and retards

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[cbender]

Thank you Sylvain for your assistance. I do agree that this is not
the ideal way to update SQL Server, but I didn't know that a DAO/ADO
recordset would do better--I'll have to look into that. (We will be
migrating the application to use pass-through queries and stored
procedures. Since that migration will be very expensive with several
hundred queries and forms to modify, this was my approach to get the
application to SQL Server and then do the migration in phases as we
are able to get funding. We were sort of against a wall because of
the database size and number of users exceeds Access' capabilities and
the application does seem to perform much better even with this less-
than-ideal architecture)

I am still somewhat puzzled as to why I am getting this behavior now
and was not back in August. When I tested it then I tested nearly
every form because I was trying to establish what performance
improvements we might expect.

Thanks again!
Cheryl

Hum, using QueryDefs to update SQL-Server through linked tables instead of
using passthrough queries or either DAO or ADO objects (recordsets,
connection object) - not sure that you have made the best choice here. Will
all these errors, maybe it's time for you to use a more conventional
approach for updating the database.

The dbFailOnError is used to make the local VBA code to fail when there is
an error message coming from SQL-Server. By default, an error from
SQL-Server is not considered as a local VBA error (the VBA code itself is
perfectly valid). There is also the possibility that the error code from
SQL-Server is just a message or a warning. Maybe displaying the local error
code could put some light on this; however, I don't know if your use of
linked tables + querydef will hide the true error code coming from
SQL-Server. Using the SQL-Server Profiler might help you in understanding
what's happening here. Instead of using the SQL-Server Profiler, you can
also try the ShowPlan option of Access/JET:http://builder.com.com/5100-6388-5064388-2.html

Make sure that you don't have strange things like nullable Bit fields (often
a problem with Access) or not-nullable fields with no default value
(shouldn't be a problem but in your case, we never know).

Finally, if I were you, I would change my method for updating the backend
database.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thanks James and Sylvain for your suggestions.

I am somewhat baffled about the role of these options and why they
behave as they do and I have not found anything that explains how
these flags work in detail. For two queries I have seen Access crash
when I was tracing through the code (that is the Application Error
occurred almost as soon as I hit the execute line in the code.) When
I removed the dbFailOnError flag the problem seems to have gone away.
This morning I was testing another form that had both flags on both
queries in an update routine. Access crashed on the first query.
Once I remove the dbFailOnError flag it did not crash. I thought
perhaps it was a timing issue as James suggested, but the fact that it
crashes now even when I trace into the code would seem to suggest
otherwise.

Sylvain had asked about architecture. I am using forms bound to
Access temporary tables (the temp tables are in a separate mdb but are
are stored locally for each user) and updating the SQL Server tables
when the user goes to another batch or lot. Some of the routines have
15-20 queries as this is a fairly complex laboratory management
information system (LIMS), while other routines may have only 1-2
queries. Below is an example of how I update SQL Server tables;

'first delete all the reagents for the batch
Set qryTemp =
CurrentDb.QueryDefs("qdelAnalysisBatchReagentsViewS10")
qryTemp.Parameters("AnalysisBatch") = flngAnalysisBatchPrev
qryTemp.Execute dbSeeChanges '+ dbFailOnError
'the query above causes Access to crash when the dbFailOnError
statment is included

'write all the reagents from temp space
Set qryTemp =
CurrentDb.QueryDefs("qappAnalysisBatchReagentsViewS20")
qryTemp.Parameters("AnalysisBatch") = flngAnalysisBatchPrev
qryTemp.Execute dbSeeChanges '+ dbFailOnError

We tend to rely heavily on stored parameter queries. Below is the SQL
for the query above (qdelAnalysisBatchReagentsViewS10)

PARAMETERS AnalysisBatch Long;
DELETE tblALAnalysisBatchReagent.*
FROM tblALAnalysisBatchReagent
WHERE
(((tblALAnalysisBatchReagent.AnalysisBatchNumber)=[AnalysisBatch]));

As far as the IDENTITY columns my keys on tables are almost never
IDENTITY columns and I don't think adding the column to the key is
feasible. I did add a non-primary key column without effect (Access
still crashed)

In summary the changes that make a consistent difference (preventing a
crash) are adding the dbSeeChanges and sometimes removing the
dbFailOnError. Can anyone tell me how to get more detail out of these
crashes? I am not finding the file that Access claims to be sending
with the report (the .crv file does not exist in the directory
mentioned. I find much older files--perhaps this is an SP2 feature).
I downloaded and installed WinDbg but am a ways from being able to use
it with any intelligence.

Thanks again for your help.
Cheryl

- Show quoted text -

 

[Sylvain Lafontaine]

You don't necessarily need to use a DAO/ADO recordset but you must make sure
that each step are done synchronously and that no more than a single
connection is used at a time and it would be even better for your to enclose
all these step in a transaction. The following page should be a good
example for you:

http://allenbrowne.com/ser-37.html

The problem with using linked tables for massive changes is that we don't
really know what Access is doing under the hood.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thank you Sylvain for your assistance. I do agree that this is not
the ideal way to update SQL Server, but I didn't know that a DAO/ADO
recordset would do better--I'll have to look into that. (We will be
migrating the application to use pass-through queries and stored
procedures. Since that migration will be very expensive with several
hundred queries and forms to modify, this was my approach to get the
application to SQL Server and then do the migration in phases as we
are able to get funding. We were sort of against a wall because of
the database size and number of users exceeds Access' capabilities and
the application does seem to perform much better even with this less-
than-ideal architecture)

I am still somewhat puzzled as to why I am getting this behavior now
and was not back in August. When I tested it then I tested nearly
every form because I was trying to establish what performance
improvements we might expect.

Thanks again!
Cheryl

Hum, using QueryDefs to update SQL-Server through linked tables instead
of
using passthrough queries or either DAO or ADO objects (recordsets,
connection object) - not sure that you have made the best choice here.
Will
all these errors, maybe it's time for you to use a more conventional
approach for updating the database.

The dbFailOnError is used to make the local VBA code to fail when there
is
an error message coming from SQL-Server. By default, an error from
SQL-Server is not considered as a local VBA error (the VBA code itself is
perfectly valid). There is also the possibility that the error code from
SQL-Server is just a message or a warning. Maybe displaying the local
error
code could put some light on this; however, I don't know if your use of
linked tables + querydef will hide the true error code coming from
SQL-Server. Using the SQL-Server Profiler might help you in
understanding
what's happening here. Instead of using the SQL-Server Profiler, you can
also try the ShowPlan option of
Access/JET:http://builder.com.com/5100-6388-5064388-2.html

Make sure that you don't have strange things like nullable Bit fields
(often
a problem with Access) or not-nullable fields with no default value
(shouldn't be a problem but in your case, we never know).

Finally, if I were you, I would change my method for updating the backend
database.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thanks James and Sylvain for your suggestions.

I am somewhat baffled about the role of these options and why they
behave as they do and I have not found anything that explains how
these flags work in detail. For two queries I have seen Access crash
when I was tracing through the code (that is the Application Error
occurred almost as soon as I hit the execute line in the code.) When
I removed the dbFailOnError flag the problem seems to have gone away.
This morning I was testing another form that had both flags on both
queries in an update routine. Access crashed on the first query.
Once I remove the dbFailOnError flag it did not crash. I thought
perhaps it was a timing issue as James suggested, but the fact that it
crashes now even when I trace into the code would seem to suggest
otherwise.

Sylvain had asked about architecture. I am using forms bound to
Access temporary tables (the temp tables are in a separate mdb but are
are stored locally for each user) and updating the SQL Server tables
when the user goes to another batch or lot. Some of the routines have
15-20 queries as this is a fairly complex laboratory management
information system (LIMS), while other routines may have only 1-2
queries. Below is an example of how I update SQL Server tables;

'first delete all the reagents for the batch
Set qryTemp =
CurrentDb.QueryDefs("qdelAnalysisBatchReagentsViewS10")
qryTemp.Parameters("AnalysisBatch") = flngAnalysisBatchPrev
qryTemp.Execute dbSeeChanges '+ dbFailOnError
'the query above causes Access to crash when the dbFailOnError
statment is included

'write all the reagents from temp space
Set qryTemp =
CurrentDb.QueryDefs("qappAnalysisBatchReagentsViewS20")
qryTemp.Parameters("AnalysisBatch") = flngAnalysisBatchPrev
qryTemp.Execute dbSeeChanges '+ dbFailOnError

We tend to rely heavily on stored parameter queries. Below is the SQL
for the query above (qdelAnalysisBatchReagentsViewS10)

PARAMETERS AnalysisBatch Long;
DELETE tblALAnalysisBatchReagent.*
FROM tblALAnalysisBatchReagent
WHERE
(((tblALAnalysisBatchReagent.AnalysisBatchNumber)=[AnalysisBatch]));

As far as the IDENTITY columns my keys on tables are almost never
IDENTITY columns and I don't think adding the column to the key is
feasible. I did add a non-primary key column without effect (Access
still crashed)

In summary the changes that make a consistent difference (preventing a
crash) are adding the dbSeeChanges and sometimes removing the
dbFailOnError. Can anyone tell me how to get more detail out of these
crashes? I am not finding the file that Access claims to be sending
with the report (the .crv file does not exist in the directory
mentioned. I find much older files--perhaps this is an SP2 feature).
I downloaded and installed WinDbg but am a ways from being able to use
it with any intelligence.

Thanks again for your help.
Cheryl

On Apr 3, 5:50 pm, "James A. Fortune" <[email protected]>
wrote:
(e-mail address removed) wrote:
I think I have solved the problem by adding the option dbSeeChanges
to
all queries that update SQL Server tables. The only thing is from
what I understand about this option this really shouldn't matter for
tables that don't have an IDENTITY column should it?

I did find some rather peculiar behavior. There were two queries
that
had a dbFailOnError + dbSeeChanges--in both of these the combined
flags cause the application to crash. Once I removed the
dbFailOnError they work fine. Other queries that have both flags
set
work fine. So I'm curious what effect the dbFailOnError has on the
ODBC query. Both are parameter queries but the tables they update
are
very typical and there is no IDENTITY column on either table.

Some info about my computer/config

Access--2003 SP2 11.6566.8107
SQL Server 2005
ODBC32.dll version 3.525.1117.0 File Version 3.525.1117.0
(xpsp_sp2_rtm.040803-2158)

odbcjt32.dll 4.0.6304.0

Operating System is XP Pro, SP2, (version 5.1 Build
2600.xpsp_sp2_rtm.
040803-2158)

Maybe dbFailOnError is about as honest as a Microsoft status bar .
I
never tried to update a SQL Server table without an IDENTITY column.
Does putting in IDENTITY columns change the results you discovered?

James A. Fortune
(e-mail address removed)- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Todos Menos [MSFT]]

Note that Tony Toews is a MDB _WUSS_ and should not be trusted for
anything.

It's like gonig to ask a 1st grader for financial advice