Access97 with SQL Server back end

[Rebecca]

We are converting my Access97 back end to SQL Server and almost have it
all working. The current problem has to do with one situation in which
I have to programmaticall y (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordse t("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordse t("SELECT * FROM _
[workshop assignments]", dbOpenDynaset, dbSeeChanges)

newrec.AddNew ' assign the workshop
newrec![ComboWS] = WSKey
newrec![Participant] = IndividualID
newrec![Priority] = WSPriority
newrec![Assigned] = True
newrec.Update
End If
Everything goes smoothly until I hit the newrec.Update and receive a
"3146 ODBC call failed" error.

We've tried:
1. checked all the permissions. I am able to add records through bound
forms just fine.
2. Changed dbOpenDynaset to other things and got different errors.
3. Added dbOptimistic locking which had no effect.

Got any other ideas?

Thanks,
Rebecca

[Rebecca]

Well, we went back to the table declaration in the SQL Server and
changed the bit fields to Allow Nulls. Problem fixed.

Thanks anyway,
Rebecca

Rebecca wrote:

We are converting my Access97 back end to SQL Server and almost have it
all working. The current problem has to do with one situation in which
I have to programmaticall y (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordse t("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordse t("SELECT * FROM _
[workshop assignments]", dbOpenDynaset, dbSeeChanges)

newrec.AddNew ' assign the workshop
newrec![ComboWS] = WSKey
newrec![Participant] = IndividualID
newrec![Priority] = WSPriority
newrec![Assigned] = True
newrec.Update
End If
Everything goes smoothly until I hit the newrec.Update and receive a
"3146 ODBC call failed" error.

We've tried:
1. checked all the permissions. I am able to add records through bound
forms just fine.
2. Changed dbOpenDynaset to other things and got different errors.
3. Added dbOptimistic locking which had no effect.

Got any other ideas?

Thanks,
Rebecca

[Tony Toews]

Rebecca <Re*****@JaxonF amily.net> wrote:

We are converting my Access97 back end to SQL Server and almost have it
all working. The current problem has to do with one situation in which
I have to programmaticall y (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordse t("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordse t("SELECT * FROM _
[workshop assignments]", dbOpenDynaset, dbSeeChanges)

newrec.AddNew ' assign the workshop
newrec![ComboWS] = WSKey
newrec![Participant] = IndividualID
newrec![Priority] = WSPriority
newrec![Assigned] = True
newrec.Update
End If

Glad you figured out your problem however this logic will waste resources and slow
things down. You are going to get a recordset of all the records in the table but
you are just adding a record.

You'd be much better off doing an INSERT ... VALUES query. Or opening the
recordset using dbAppendOnly.

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

[Tom van Stiphout]

On Sun, 05 Sep 2004 22:46:48 GMT, Rebecca <Re*****@JaxonF amily.net>
wrote:

I wonder what would happen if you don't allow nulls, and write:
newrec![Assigned] = 1 ' bit can only be 1 or 0, not -1

If that works that would be far preferrable in most applications. It's
hard to imagine what it means for Assigned to be Null, rather than
Assigned or Not_Assigned. Perhaps it would indicate "I don't know".

Only in rare situations should you weaken the database design to
support front-end programming limitations.

-Tom.

Well, we went back to the table declaration in the SQL Server and
changed the bit fields to Allow Nulls. Problem fixed.

Thanks anyway,
Rebecca

Rebecca wrote:

We are converting my Access97 back end to SQL Server and almost have it
all working. The current problem has to do with one situation in which
I have to programmaticall y (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordse t("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordse t("SELECT * FROM _
[workshop assignments]", dbOpenDynaset, dbSeeChanges)

newrec.AddNew ' assign the workshop
newrec![ComboWS] = WSKey
newrec![Participant] = IndividualID
newrec![Priority] = WSPriority
newrec![Assigned] = True
newrec.Update
End If
Everything goes smoothly until I hit the newrec.Update and receive a
"3146 ODBC call failed" error.

We've tried:
1. checked all the permissions. I am able to add records through bound
forms just fine.
2. Changed dbOpenDynaset to other things and got different errors.
3. Added dbOptimistic locking which had no effect.

Got any other ideas?

Thanks,
Rebecca

[Rebecca]

Tony Toews wrote:

Rebecca <Re*****@JaxonF amily.net> wrote:

We are converting my Access97 back end to SQL Server and almost have it
all working. The current problem has to do with one situation in which
I have to programmaticall y (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordse t("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordse t("SELECT * FROM _
[workshop assignments]", dbOpenDynaset, dbSeeChanges)

newrec.AddNew ' assign the workshop
newrec![ComboWS] = WSKey
newrec![Participant] = IndividualID
newrec![Priority] = WSPriority
newrec![Assigned] = True
newrec.Update
End If

Glad you figured out your problem however this logic will waste resources and slow
things down. You are going to get a recordset of all the records in the table but
you are just adding a record.

You'd be much better off doing an INSERT ... VALUES query. Or opening the
recordset using dbAppendOnly.

Tony
--

Tony,

This sounds like a much better solution. We were worrying about the
performance aspect of doing this function across the web. Thanks for
your advice.

Rebecca

[Rebecca]

Actually there was never a real reason why it was not allowed to be
null, so it doesn't bother us to allow them now. These records are
always going to have the "assigned" bit set to true, it's the three
other boolean fields that aren't relevant in this procedure that we're
allowing to default to false by simply not setting them at all. Am I
right that that's what will happen?

Thanks,
Rebecca

Tom van Stiphout wrote:

On Sun, 05 Sep 2004 22:46:48 GMT, Rebecca <Re*****@JaxonF amily.net>
wrote:

I wonder what would happen if you don't allow nulls, and write:
newrec![Assigned] = 1 ' bit can only be 1 or 0, not -1

If that works that would be far preferrable in most applications. It's
hard to imagine what it means for Assigned to be Null, rather than
Assigned or Not_Assigned. Perhaps it would indicate "I don't know".

Only in rare situations should you weaken the database design to
support front-end programming limitations.

-Tom.

Well, we went back to the table declaration in the SQL Server and
changed the bit fields to Allow Nulls. Problem fixed.

Thanks anyway,
Rebecca

Rebecca wrote:

We are converting my Access97 back end to SQL Server and almost have it
all working. The current problem has to do with one situation in which
I have to programmaticall y (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordse t("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordse t("SELECT * FROM _
[workshop assignments]", dbOpenDynaset, dbSeeChanges)

newrec.AddNew ' assign the workshop
newrec![ComboWS] = WSKey
newrec![Participant] = IndividualID
newrec![Priority] = WSPriority
newrec![Assigned] = True
newrec.Update
End If
Everything goes smoothly until I hit the newrec.Update and receive a
"3146 ODBC call failed" error.

We've tried:
1. checked all the permissions. I am able to add records through bound
forms just fine.
2. Changed dbOpenDynaset to other things and got different errors.
3. Added dbOptimistic locking which had no effect.

Got any other ideas?

Thanks,
Rebecca

[Tom van Stiphout]

On Mon, 06 Sep 2004 02:35:13 GMT, Rebecca <Re*****@JaxonF amily.net>
wrote:

No. And you can easily verify that. If you allow null, and don't
provide a value, the field will be null once your update is finished.

You can set a default value of 0 to indicate False, and then make the
field required by setting Allow Nulls to False. That's what I would
recommend.

-Tom.

Actually there was never a real reason why it was not allowed to be
null, so it doesn't bother us to allow them now. These records are
always going to have the "assigned" bit set to true, it's the three
other boolean fields that aren't relevant in this procedure that we're
allowing to default to false by simply not setting them at all. Am I
right that that's what will happen?

Thanks,
Rebecca

Tom van Stiphout wrote:

On Sun, 05 Sep 2004 22:46:48 GMT, Rebecca <Re*****@JaxonF amily.net>
wrote:

I wonder what would happen if you don't allow nulls, and write:
newrec![Assigned] = 1 ' bit can only be 1 or 0, not -1

If that works that would be far preferrable in most applications. It's
hard to imagine what it means for Assigned to be Null, rather than
Assigned or Not_Assigned. Perhaps it would indicate "I don't know".

Only in rare situations should you weaken the database design to
support front-end programming limitations.

-Tom.

Well, we went back to the table declaration in the SQL Server and
changed the bit fields to Allow Nulls. Problem fixed.

Thanks anyway,
Rebecca

Rebecca wrote:

We are converting my Access97 back end to SQL Server and almost have it
all working. The current problem has to do with one situation in which
I have to programmaticall y (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordse t("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordse t("SELECT * FROM _
[workshop assignments]", dbOpenDynaset, dbSeeChanges)

newrec.AddNew ' assign the workshop
newrec![ComboWS] = WSKey
newrec![Participant] = IndividualID
newrec![Priority] = WSPriority
newrec![Assigned] = True
newrec.Update
End If
Everythin g goes smoothly until I hit the newrec.Update and receive a
"3146 ODBC call failed" error.

We've tried:
1. checked all the permissions. I am able to add records through bound
forms just fine.
2. Changed dbOpenDynaset to other things and got different errors.
3. Added dbOptimistic locking which had no effect.

Got any other ideas?

Thanks,
Rebecca

[Trevor Best]

Rebecca wrote:

Well, we went back to the table declaration in the SQL Server and
changed the bit fields to Allow Nulls. Problem fixed.

Bad idea when using an Access front end, you should set bit fields to
NOT NULL and default to 0. The reason being in Access, a Yes/No field
cannot be null, if you bind any forms to this table with nulls in bit
fields you will end up in all sorts of trouble.

BTW, in trapping ODBC Errors...

<---
Dim e As Error, strODBCError As String
On Error Goto ErrTrap
.... ' do stuff

ErrTrap:
Select Case Err.Number
Case 3146
For each e in DbEngine.Errors
strODBCError = strODBCError & e.Description & VbCr
Next
MsgBox strODBCError,16 ,"Oops"
Resume ...
Case ...
... Normal Error handling
End Select
--->
--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[Rebecca]

Hmmm, I've actually made quite a few of those records since I last
wrote, and even with those nulls in the bit fields it doesn't seem to
mind. When I display that table, they display as "0", and when those
records are represented in the subform where they appear after I've
created them, it doesn't cause a problem.

I do see what you mean though, and agree that it would make the code
more correct if I were to change it the way you say.

Thanks for the advice about trapping ODBC errors. We've noticed that
the error descriptions it gives are pretty vague. Your procedure will
be very helpful.

Rebecca

Trevor Best wrote:

Rebecca wrote:

Well, we went back to the table declaration in the SQL Server and
changed the bit fields to Allow Nulls. Problem fixed.

Bad idea when using an Access front end, you should set bit fields to
NOT NULL and default to 0. The reason being in Access, a Yes/No field
cannot be null, if you bind any forms to this table with nulls in bit
fields you will end up in all sorts of trouble.

BTW, in trapping ODBC Errors...

<---
Dim e As Error, strODBCError As String
On Error Goto ErrTrap
... ' do stuff

ErrTrap:
Select Case Err.Number
Case 3146
For each e in DbEngine.Errors
strODBCError = strODBCError & e.Description & VbCr
Next
MsgBox strODBCError,16 ,"Oops"
Resume ...
Case ...
... Normal Error handling
End Select
--->