Search results

I've pinpointed the issue finally! It wasn't the recordsets per se - I tried inserting same values in an append query and it would not allow due to INCOMPATIBLE DATA TYPES. So I keep coming back to the same issue - date/time field does not allow certain values if formatted to datetime2. So I've...

1. My code generally is: Set db = CurrentDb Set rs = db.OpenRecordset("tblJobLog", dbOpenDynaset, dbSeeChanges) rs.AddNew rs!fldJLOrderID = Me.fldOrderID rs!fldJLStaffID = Forms!frmlogin!cmbstaff rs!fldJLDateTime = Now() rs!fldJLNote = txt rs.update...

Data type is time (I thought that would be better than datetime which defaults to 1899).

The table has 8000+ records. How would I be able to single that out?

Hi, Since I've converted to SQL backend I've not been able to display the AM/PM in the fields with times although they are formatted as time data type. I tried the Medium date format as well hh:nn AM/PM but no success. Its probably an easy one but can anyone suggest how to resolve please?

Hi, I am having another issue with the SQL server. Using SSMS I am trying to change datetime2 (default from Access migration) data type to datetime (works better for many calculations/queries). It works for some fields but for others I get the following error message. Is there any way around this?

So its really 2 issues: 1. I'm currently testing the prototype DB on the Server itself and it won't allow recordsets to create new records. ODBC call failed - even when I use dbSeeChanges Further to this issue I have locate the following info which seems to nail it but I'm quite new to SQL...

Thank you. Now I am having another weird issue. The code to add new record in recordset works fine on my computer with SQL Server backend. Then when I copy DB to the computer which will be running it - still local but different SQL server instance it keeps bringing up error 3146. Any ideas what...

Yes I think it was the audit trail in my DB as well. But have just temporarily removed the code - definitely need to address the underlying issue here. And thanks for bringing this up as I discovered from this thread how dbSeechanges can help sort issues I've been having.

Thanks I think I've finally resolved it! I realised there were some flaws with the SQL when I tried running in SSMS it showed the errors. Learnt a lot along the way though:)

Yes that is correct.

Yes that has has definitely solved SOME errors I've been having! But can't use dbSeeChanges in my code for the queryDef - so how would I do this? The code to change the WHERE clause is here. If there is a different way to achieve this whilst using the dbSeeChanges I would be happy to try. Set...

I don't think so. But it is a pass-through query if that makes a difference? I'm also having trouble with opening recordsets to add new record in vba - seems to say it can't make changes to linked table?

Hi, I am working on moving a DB to SQL server back end but there are a couple of errors which I am having trouble resolve. In particular there is one form that has the following code when opens to change a querydef. The query runs fine but these lines of code keep flagging an error and I'm not...

Hi I have this error flagging up practically every before and after update event of one table in my DB. I just trapped the error but was wondering if there was any way to completely avoid it. Other errors which persist since linking to SQL Server tables are Error 91 whenever I try adding new...

I don't think you should have a space before the .FileDialog

Hi, I have not used Visual Studio in the past and I'm attempting to create a two-way sync with the database in SQL server. I understand this is not explored much on this forum, but interested in hearing suggestions on tutorials or other help out there...

So is Sunday the default?

That sounds like what I am looking for. How would I integrate it into a SELECT query?

No I've seen that - it only works in MySQL not SQL Server