x

ODBC call failed for recordset .Bookmark

I tried this code in Access 2000, using a SQL Server 2008 Express backend:

Dim rst As DAO.Recordset
...
Set rst = gdbTables.OpenRecordset("SELECT * FROM tblSCInbox", dbOpenDynaset, dbSeeChanges)
With rst
.AddNew
!Sequence = CLng(Rnd * 1000000) + 1
!InitiatorID = glngUserID
!SCID = lngSCID
!FullSubject = Me.txtComposeSubject
!Message = Me.txtComposeMessage
.Update 
.Bookmark = .LastModified

the record gets inserted fine into tblSCInbox, but I get this error after ".Bookmark = .LastModified":

Error 3146: ODBC--call failed

and DBEngine.Errors(0).Description is:

[Microsoft][SQL Server Native Client 10.0]Invalid time format
There are two datetime2 columns that have default of getdate(), and they got inserted fine, I'm not sure what's wrong.
more ▼

asked May 27 '11 at 12:44 PM in Default

EuniceH gravatar image

EuniceH
83 8 8 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I get the feeling there's a bit of code missing...

What's the definition of tblSCInbox? (ie what columns, and data types)?

Can you trace the SQL being passed between Access & SQL Server? What does that show? edit this is done using SQL Server Profiler http://msdn.microsoft.com/en-us/library/ms181091.aspx, http://msdn.microsoft.com/en-us/library/ms187929.aspx. If you search for SQL Server Profiler (http://www.google.co.uk/search?q=sql+server+profiler), you'll find all sorts of useful resources - videos, blog posts etc - that talk you through the process.

I also suspect that Access has some nasty date/time restrictions, but it's been so long since I last programmed against it, I really can't remember.
more ▼

answered May 27 '11 at 01:04 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 14 20 44

I'm not sure about your first question, tblSCInbox is a table. How can I trace the sql passed between Access and SQL Server? Thanks for your reply!
May 27 '11 at 01:11 PM EuniceH
The Not Null columns of tblSCINbox are: SCInboxID (Identity = True) InitialDTS (PK, datetime2(0), not null) --has default of ALTER TABLE [dbo].[tblSCInbox] ADD DEFAULT (getdate()) FOR [InitialDTS] Sequence (PK, int, not null) SCID (int, not null) FullSubject (nvarchar(100), not null) and InitiatorID is (int, null)
May 27 '11 at 01:21 PM EuniceH
I just installed AnjLab SQL Profiler, since Express doesn't have SQL Profiler or any Performance tools. It so far doesn't tell me much. I'll have a look at the resources you mentioned though.
May 27 '11 at 02:07 PM EuniceH
is there any way to attach a gif? I have the profiler data.
May 27 '11 at 02:27 PM EuniceH

I replaced ".Bookmark = .LastModified"

with a query to select the TOP 1 from the table.
May 27 '11 at 03:42 PM EuniceH
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x562
x48
x37

asked: May 27 '11 at 12:44 PM

Seen: 1710 times

Last Updated: May 27 '11 at 01:02 PM