Solved

ERROR: [ODBC Microsoft Access Driver]COUNT field incorrect

Posted on 2002-07-08
6
607 Views
Last Modified: 2008-02-26
I am writing a conversion to convert approx 130 access databases to SQL.  I have already created my SQL db, build the application for the front end and have converted a few of the databases on a "1 at a time" basis.  To expedite the process, I have created a dynamic conversion.  Items involved are a table listing all of the access dbs that need converted, 2 sql statements needed to pull the necessary data to convert and the location of the db.  It works perfectly until I use the 2nd sql statement.  I am getting an ODBC error: COUNT field incorrect.  The baffling part is that I am not returning a count, and I am opening it in a recordset, so there is no mismatch of values & variables, or anything like that.  I know that it isn't my connection because the first statement (to the same db using the same connection) works fine.  

I cannot find ANY information anywhere within Microsofts "help" sites.  Please, if anyone has any ideas, please share.  This has been a 7 month project and is due to go live next Monday.  Without data, I'm hosed!  
Thank you so much!
0
Comment
Question by:MisConFit8
6 Comments
 
LVL 12

Expert Comment

by:Wouter Boevink
ID: 7137892
Can you post the sql statements?
0
 

Author Comment

by:MisConFit8
ID: 7137908
Note: the ID is being attatched dynamically in the active server page.  
This is the "full" statement as displayed by a response.write in the application page:
Select [Invite Sent] as InviteSent, [Date Travel Form Rec'd] as TravelFormRcvd, [Arrival Date] as Arrival, [Depature Date] as Depature, [Date Survey Rec'd?] as SurveyRcvd, [Date Agreement Rec'd?] as AgreementRcvd, [Date Expense Report] as ExpenseRptRcvd, Attended, Comments, [Photo #] as PhotoNum, [Confirmation Sent Date] as CPacketSent, [Invitation Accepted] as InviteAccept, Guest1 as GuestName FROM Participants WHERE ID = 1

I have chopped this down by taking out many of the variables and adding back in one at a time.  It works fine until the 5th one is added to the statement.

I should also note that I have copied this right off of the response.write and ran it in the associated Access database and it executes without a problem.

Thank you!!!!!!!
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 7139165
how are you opening the db's? are you linking to each table in each tb dynamically or are you "opening" each db through automation?

if you are using automation, are you sure that you're setting the db reference to nothing and closing it before resetting it? such as:

Dim a As Database

Set a = Workspaces(0).OpenDatabase("c:\a.mdb", , False)
a.close
Set a = Nothing

(assuming a reference to DAO or similar)

if you are linking the tables dynamically, how are you linking them? specific code would be nice to see.

if OTHER, what is the process you're following? can you outline it / post some code snippits???

cheers,

dovholuk
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 12

Expert Comment

by:Wouter Boevink
ID: 7139874
If you leave out the 5th select does it work?
Maybe the problem is with [date ........]
Try a select on [date ........] if this doesn't work try copying the database and renaming the [date .......] column and the do a select on the new column name again.

It doesn't solve your problem of converting 130 databases but than you know where the problem is.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7260154

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7296325
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now