By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

Access97 with SQL Server back end

P: n/a
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 programmatically (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordset("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordset("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

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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 programmatically (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordset("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordset("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


Nov 13 '05 #2

P: n/a
Rebecca <Re*****@JaxonFamily.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 programmatically (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordset("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordset("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
Nov 13 '05 #3

P: n/a
On Sun, 05 Sep 2004 22:46:48 GMT, Rebecca <Re*****@JaxonFamily.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 programmatically (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordset("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordset("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


Nov 13 '05 #4

P: n/a
Tony Toews wrote:
Rebecca <Re*****@JaxonFamily.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 programmatically (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordset("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordset("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

Nov 13 '05 #5

P: n/a
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*****@JaxonFamily.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 programmatically (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordset("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordset("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



Nov 13 '05 #6

P: n/a
On Mon, 06 Sep 2004 02:35:13 GMT, Rebecca <Re*****@JaxonFamily.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*****@JaxonFamily.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 programmatically (still in the Access97 front end) add a
record to an existing table. Here is the code:

If arg = cintBatchReg Then
Set newrec = db.OpenRecordset("SELECT * FROM [workshop assignments]", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordset("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



Nov 13 '05 #7

P: n/a
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.
\__\

Nov 13 '05 #8

P: n/a
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
--->


Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.