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, 2011 at 12:44 PM in Default

avatar image

EuniceH
84 8 8 12

(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, 2011 at 01:04 PM

avatar image

ThomasRushton ♦♦
40.3k 20 49 53

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, 2011 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, 2011 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, 2011 at 02:07 PM EuniceH

is there any way to attach a gif? I have the profiler data.

May 27, 2011 at 02:27 PM EuniceH

I replaced ".Bookmark = .LastModified"

with a query to select the TOP 1 from the table.

May 27, 2011 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.

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:

x748
x54
x47

asked: May 27, 2011 at 12:44 PM

Seen: 2503 times

Last Updated: May 27, 2011 at 01:02 PM

Copyright 2016 Redgate Software. Privacy Policy