Solved

ERROR: [ODBC Microsoft Access Driver]COUNT field incorrect

Posted on 2002-07-08
6
634 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Back Up Your Microsoft Windows Server®

Back up 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

730 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