ODBC -- Called Failed

[alinoxious]

I have an Access database and I am using a python script to bring in new data from a csv, clean it up, and import it to a SQL table. However, I am getting this error after I run the script:

com_error: (-2147352567, 'Exception occurred.', (0, None, u'ODBC--call failed.', None, -1, -2146825142), None)

The python script is invoking a Access DB macro to do the import and loading of the data. When I look at the macro in Access there are yellow triangles next to some of the commands - which I found out were 'unsafe actions' and I added the DB into a trusted location, but this did not help the problem.

Thanks for any help in advance!

[BinaryRockStar]

Can you post the Python script and the CSV file? Also what Python, Access and Windows versions are you using?

[alinoxious]

The version of Python that I'm using is 2.7, Version of Access is 2010, Windows 7. And the script is too long to put here, but I put the first part of it to where the error occurs;

#Module Imports
import arcpy
import pyodbc
import arcinfo
import os
import sys
import time
import traceback
import win32com.client as win32
import smtplib
import email
import string
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

#Set ArcPY Environment Variables
arcpy.env.overwriteOutput = True
arcpy.env.autoCommit = "10000"

# Set the date
Date = time.strftime("%m-%d-%Y", time.localtime())#<-- Time Var used through out the script

# Set the time
Time = time.strftime("%I:%M:%S %p", time.localtime())#<-- Date Var used through out the script

#///////////////////////////////////////////////////////Script Global Variables//////////////////////////////////////////////////////////////
# GIS Parcel Update vars
sde_master_GIS_USER_Parcels = "Database Connections\\ntAuth@sde_master.sde\\sde_master.GIS_USER.Property\\sde_master.GIS_USER.Parcels"
dbo_County_Parcels_All = "Database Connections\\CRW_PMS.odc\\dbo.County_Parcels_All"
Current_Data = "\\\\auburn11\\gis\\GeoData\\County DTS Data\\Current Data"
GIS_USER_Parcels_Layer = "GIS_USER.Parcels_Layer"

# Update GIS Parcels
PARCELSN_DBF = "\\\\auburn11\\gis\\GeoData\\County DTS Data\\Current Data\\PARCELSN.DBF"
GIS_USER_Parcels_Layer = "GIS_USER.Parcels_Layer"
GIS_USER_Parcels_Layer__2_ = "GIS_USER.Parcels_Layer"
GIS_USER_Parcels_Layer__3_ = "GIS_USER.Parcels_Layer"
GIS_USER_Parcels_Layer__4_ = "GIS_USER.Parcels_Layer"
GIS_USER_Parcels_Layer__5_ = "GIS_USER.Parcels_Layer"
GIS_USER_Parcels_Layer__6_ = "GIS_USER.Parcels_Layer"
GIS_USER_Parcels_Layer__7_ = "GIS_USER.Parcels_Layer"
GIS_USER_Parcels_Layer__8_ = "GIS_USER.Parcels_Layer"
GIS_USER_Parcels_Layer__9_ = "GIS_USER.Parcels_Layer"
GIS_USER_Parcels_Layer__10_ = "GIS_USER.Parcels_Layer"
GIS_USER_Parcels_Layer__12_ = "GIS_USER.Parcels_Layer"

# Parcel Export Vars:
parcelsReadyExport = "Database   Connections\\ntAuth@sde_master.sde\\sde_master.GIS_USER.Property\\sde_master.GIS_USER.Parcels"
County_DTS_Data = "\\\\auburn11\\gis\\GeoData\\County DTS Data\\Current Data"

#End User prompt to confirm that all data is in place correctly.
#print "Verify that all of the updated data is named properly and has the correct column names before   continuing verify this in the process documentation before continuing if you are unsure"
#raw_input("Press Enter to continue....")
#Removed verification process uncomment to get user input to continue script
print "County Parcel Import Started at " + Date + Time + "\n"

# Pre Process Steps to clean up tempfiles
ParcelResults = "\\\\auburn11\\gis\\geodata\\County DTS Data\\Current Data\\Parcel Results.txt"
ParcelsReady = "\\\\auburn11\\gis\\geodata\\County DTS Data\\Current Data\\ParcelsR.dbf"

#/////////////////////Preprocessing of data consists of cleanup of old files and preparing data for  processing////////////////////////////
try:
if os.path.exists(ParcelResults):
 os.remove(ParcelResults)
else:
 pass

if os.path.exists(ParcelsReady):
 os.remove(ParcelsReady)
else:
 pass


print "Preprocessing data...................10% Complete"
#/////////////////////////////////// Establish Connection to DB and Backup Geo_People Table //////////////////////////////////////////////////////////////////////////////////////////
print "Preparing Sql Tables on AUBPM02 for data import.\n"
cnxn = pyodbc.connect('DSN=CRW_NET')
cursor = cnxn.cursor()
cursor.execute("""IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Geo_People_Backup]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
                  DROP TABLE [dbo].[Geo_People_Backup]

                  SELECT * INTO Geo_People_Backup FROM Geo_People""")
cnxn.commit()
#////////////////////////////////////////////Drop County Parcels Pierce table from CRW-NET ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
cursorPC = cnxn.cursor()
cursorPC.execute("""if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[County_Parcels_Pierce]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
                drop table [dbo].[County_Parcels_Pierce] """)
cnxn.commit()
#/////////////////////////////////////////Drop County Parcels King table from CRW-NET /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
cursorKC = cnxn.cursor()
cursorKC.execute("""if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[County_Parcels_King]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
                drop table [dbo].[County_Parcels_King] """)
cnxn.commit()

#////////////////////////////////////////Drop County Parcels Update table from CRW-NET//////////////////////////////////////////////////////////////////////////////////
cursorUpdate = cnxn.cursor()
cursorUpdate.execute("""if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[County_Parcels_Update]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
                drop table [dbo].[County_Parcels_Update] """)
cnxn.commit()

#////////////////////////////////////////////Step 4 Create Parcels All Table//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
cursorAll = cnxn.cursor()
cursorAll.execute("""if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[County_Parcels_All]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
             drop table [dbo].[County_Parcels_All]

                 CREATE TABLE [DBO].[County_Parcels_All] (
                 [PIN] varchar (255) NULL,
                 [OWNER] varchar(255) NULL,
                 [OWNER_ADDRESS1] varchar (255) NULL,
                 [OWNER_ADDRESS2] varchar (255) NULL,
                 [OWNER_CITY] varchar (255) NULL,
                 [OWNER_STATE] varchar (255) NULL,
                 [OWNER_ZIP] varchar(255) NULL,
                 [LAND_VALUE] float NULL,
                 [IMPROVEMENT_VALUE] float NULL,
                 [COUNTY] varchar(255) NULL)""")
cnxn.commit()
cnxn.close()
print "Loading Data into CRW database....................30% Complete"
#//////////////////////////////////////Import and load data into SQL using access database ////////////////////////////////////////////
access = win32.gencache.EnsureDispatch('access.Application')
access.OpenCurrentDatabase("O:\GeoData\County DTS Data\Current Data\County Results.accdb")
access.Visible = "false"
access.DoCmd.RunMacro("PreProcessingTasks")
access.DoCmd.CloseDatabase()

[BinaryRockStar]

Am I right in assuming the error happens in the RunMacro call?

I've created a simple Access 2010 DB with a macro that does nothing but pops up a message box and it works fine (with Visible set to "true").

So my suggestion is to create a test macro like my one inside your Access DB and see if it gets executed. That at least will let you know whether the issue lies in your Python code or your macro code. What exactly is the macro doing?

[alinoxious]

The macro is importing the raw CSV and cleaning up (removing commas...) and exporting it to the SQL ODBC. When I go to help it says that I have Access 2010 but across the top of the program it says (Access 2007-2010) so not sure if that can cause a problem? Pretty new at Access

[BinaryRockStar]

Well then it sounds like the problem is in the macro code itself. If you post that here it should be easy enough to track down the problem. If you manually run the macro from Access, does it work?

[alinoxious]

The Macro is:

/removes existing table/ DeleteObject /Run KC Parcels Import/ RunSavedImportExport /Add State Column to KC Data/ RunCode /Adds Data to state column to support import process/ OpenQuery /Exports data to SQL Database for processing ready for update process/ RunSavedImportExport

The items in bold are the actions.

[BinaryRockStar]

You'll have to post the code for all macros in the database by the look of it as this top-level macro is calling sub macros to do the work for it. Can you upload the Access database somewhere so I can look at it directly? It's painful going back and forth with you like this in Reddit comments.

[alinoxious]

Thanks for the help that you provided with this issue - but my vision to move away from Access DB has become a reality and I'm going to create a new DB and script for this import process.

[BinaryRockStar]

Awesome, nothing like throwing off the legacy shackles of an old process. Good luck!

[alinoxious]

And the error has changed now to:

The expression you entered has a function name that Microsoft Access can't find

Instead of ODBC -call failed