Avatar of gotiva
gotiva asked on

Run-Time error message 3146

If I run following VBA code in MS Access to create temp table for smaller data range (jan-march) it works fine, but if I run it for the whole year (really large amount of data), I am getting message:
Run-Time error 3146
ODBC call failed

The VBA code is:

Public Function CreateTable1()
' Create temp table
Dim sSQL, StartDate, EndDate
StartDate = InputBox("Enter Pay Period Start date")
EndDate = InputBox("Enter Pay Period End date")

sSQL = "SELECT *  INTO tblTempAllocs"
sSQL = sSQL & " FROM  ValAllocs "
sSQL = sSQL & " Where (ValAllocs.PPEndDate) Between #" & StartDate & "# And #" & EndDate & "#"

CurrentDb.Execute sSQL
End Function

My question is why am I getting error message and what should I do to avoid it?
Microsoft Access

Avatar of undefined
Last Comment
gotiva

8/22/2022 - Mon
Jim Horn

Your SQL looks correct.  Let's try some validating, and eyeballing your SQL statement

Public Function CreateTable1()
' Create temp table
Dim sSQL as String, StartDate as String, EndDate as String  '<-- Spell out the type of each variable
StartDate = InputBox("Enter Pay Period Start date")
EndDate = InputBox("Enter Pay Period End date")

If Not IsDate(StartDate) then
   msgbox "Your start date is not a valid date.  Try again."
   exit function
end if

If Not IsDate(EndDate) then
   msgbox "Your end date is not a valid date.  Try again."
   exit function
end if

sSQL = "SELECT *  INTO tblTempAllocs"
sSQL = sSQL & " FROM  ValAllocs "
sSQL = sSQL & " Where (ValAllocs.PPEndDate) Between #" & StartDate & "# And #" & EndDate & "#"

debug.print sSQL   '<-- If you get the same error message, copy-paste the contents of the Immediate Window (Ctrl-G) into this question.

CurrentDb.Execute sSQL
End Function
ASKER
gotiva

I executed the above code and soon after I got the same message:
Run-Time error 3146
ODBC call failed

Would that be possible that MS Access can not process large amount of data?
I don't know what else could cause the problem. MS Access is used as the front-end of Sybase database. As already mentioned, if I try to full out smaller amount of data (Dec-Feb) but if I try to pool out the data for the whole year, it gives me above mentioned message.
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
gotiva

I checked and the file size is well below 1GB.
The Immediate Window content is:

SELECT *  INTO tblTempAllocs FROM vALaLLOCS Where (ValAllocs.PPEndDate) Between #10/13/2007# And #05/10/2008#

When I get the messsage:
Run-Time error 3146
ODBC call failed

"imhorn' thanks for trying to help me out with this problem.

....and I click debug, the 'CurrentDb.Execute sSQL' part of the code gets highlighted.



I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
gotiva

I am still having the same problem, getting the same message when running the SQL statement.
The message is still the same:
Run-Time error 3146
ODBC call failed
ASKER
gotiva

Can anyone help me with this issue?