Hey Folks,
Been banging my head against this for a few days now, and can't figure out what the issue is, so hoping someone here can help me.
A quick background- building an application that has a SQL backend (2008 R2) to store transactional data, a MS Access 2010 DB that has linked tables to the SQL DB that has some other tables with some forms built as a sort of 'administrative console', and an Excel 2010 (.xslm) workbook that is accessed by end users. Based on some user input in the Excel workbook, a dynamic query is built with VBA, then passed to the Access DB.
The application works fine when users with Excel 2010 use it, however users with Excel 2007 seem to be having issues.
When they try to run the portion of the application that calls to the DB, the following error is kicked:
" Run-time error '3146' ODBC -- call failed. "
Upon stepping through the DB engine errors collection, this is the error being tripped:
" ODBC -- call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (#241) "
The code is below (stripped out some server/db names, and some irrelevant code that sits in between where the query is built & where it is actually executed):
The error is being tripped on the last line above, but again it only seems to be happening for Excel 2007 users, not 2010 users- any ideas?
Thanks all!
Been banging my head against this for a few days now, and can't figure out what the issue is, so hoping someone here can help me.
A quick background- building an application that has a SQL backend (2008 R2) to store transactional data, a MS Access 2010 DB that has linked tables to the SQL DB that has some other tables with some forms built as a sort of 'administrative console', and an Excel 2010 (.xslm) workbook that is accessed by end users. Based on some user input in the Excel workbook, a dynamic query is built with VBA, then passed to the Access DB.
The application works fine when users with Excel 2010 use it, however users with Excel 2007 seem to be having issues.
When they try to run the portion of the application that calls to the DB, the following error is kicked:
" Run-time error '3146' ODBC -- call failed. "
Upon stepping through the DB engine errors collection, this is the error being tripped:
" ODBC -- call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (#241) "
The code is below (stripped out some server/db names, and some irrelevant code that sits in between where the query is built & where it is actually executed):
Code:
Sub CalculateLineItems()
Dim lastrow As Long
Dim queryFilter As String
Dim BudgetName As String
Dim sliceLevel As String
Dim NoLineItems As Boolean
Dim AccessDB As Access.Application
Dim sqry As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim BudgetTool As Workbook
Dim NewBudget As Workbook
Set rs = New ADODB.Recordset
Set BudgetTool = ActiveWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
If Sheets("Communities").Range("J1").Value <> 0 Then
sliceLevel = "Communities"
Sheets("Communities").Visible = True
Sheets("Communities").Activate
Range("J2").Value = "=VLOOKUP(J1,A:H,5,FALSE)"
Range("J3").Value = "=VLOOKUP(J1,A:H,7,FALSE)"
Range("J4").Value = "=VLOOKUP(J1,A:H,2,FALSE)"
BudgetName = Replace(Range("J4").Value, "| ", "")
Range("J5").Value = "=VLOOKUP(J3,AllBudgetDetails!A:S,18,FALSE)"
Range("J6").Value = "=VLOOKUP(J3,AllBudgetDetails!A:S,19,FALSE)"
Range("J5:J6").NumberFormat = "mm/dd/yyyy"
queryFilter = "SolomonCode = '" & Range("J2").Value & "' AND CommunityGUID = '" & Range("J3").Value & "' AND IncentiveDate >= #" & Range("J5").Value & "#" ' AND IncentiveDate <= #" & Range("J6").Value & "#"
End If
' there is some handling here that deals with other scenarios, but it doesn't relate to this error
Set AccessDB = CreateObject("Access.Application")
AccessDB.OpenCurrentDatabase ("\\DB_Path\DB.mdb")
sqry = "INSERT INTO tmptable1 " & _
"SELECT BunchofFields FROM Transactions " & _
"WHERE " & queryFilter & " ORDER BY OrderFields;"
AccessDB.CurrentDb.Execute sqry ' This is the line where the error is trippedThe error is being tripped on the last line above, but again it only seems to be happening for Excel 2007 users, not 2010 users- any ideas?
Thanks all!