C/ODBC ADO & SQL problems

MLW222

Tried importing data (Posted Invoice & Cr Memo tables & Posted lines tables) to bring data in from NF 2.00 into Excel 2000, using C/ODBC and

First case: using SQL (following example on Navision CD)
Problem here was that no matter what I tried I could not get it to bring in more than 16384 lines. MS says 16384 is max number of lines for Excel 97 and fixed it for Excel 2000, but couldn't not get more than 16384 lines.
It would process the additional data but it just owuld not write the rows.

So, I swithc to:
Second Case: using ADO
Problem here is that, I can get the data for the Invoice Header Table and the Cr Memo Header table, and then the Invoice Line table but when it gets to the point of doing the Cr Memo Line table I get the following errors:

"The instruction at 02d32502 referenced memory at fffffffff
The memory could not be read from."

followed by:
"Slave caused an invalid page fault in module dbm.dll at 0167:02d32502"

At first, I got this only a couple of times, but got it all the time. So I did clean install of Windows 98, Navision, Office 2000 & C/ODBC

Since clean install get it all the time.

I can have routine skip Cr Memo Lines and it works fine or alternatively have it skip Invoice Lines and do Cr Memo Lines, also works fine.

What can I do?

DjangMan

This isn't the answer you're looking for but according to Navision US, ADO isn't supported connecting to C/ODBC. I had an application that ran just fine in 3.01B using Delphi 6, ADO and C/ODBC. The database converted to 3.10B and the application couldn't write more than 4 records to the database before it crashed.

So I don't know if searching around will be a good use of your time or whether you should drop 'down' to using DAO, which is supported.

DjangMan

MLW222

DjangMan

Thanks for tip.
I am using ADO 2.5 and post elsewhere suggested ADO 2.0 would work better.

This is getting kind of funny.
Started using SQL, and in addition to line limit, VB Help on SQL functions says "Do not use SQL, use ADO instead..."
Reason being ADO is newer/update. OK get that.
So go to ADO 2.5 and get suggestion to try 2.0 and now you say go back to DAO...
As I said kind of funny...next someone will suggestion going to back to Stone & chisel or papyrus.

Also, do you know why the 16384 limit in Excel 2000? As noted Excel 97 has inherent limit. Wondering if C/ODBC has the limit too, which MS says some drivers do.

MLW222

Flakey, Flakey & more flakey!

OK, tried several things.

Before I get to them though:
I had been running my attempts on Win 98, on a single user "testdatabase" (testdb), on local hard drive.

We have a multi-user, "livedatabase" (livedb), which I had not tried it on, as did not want to mess up network/live data.

Copied over Excel to a Win2K box.
Set up C/ODBC from NF CD on win2k box.
Configured C/ODBC on win2k for livedb, no testdb on that machine.

Ran VB-ADO2.5-C/ODBC report on win2k machine on livedb
It worked!

Thought, Ok, maybe something to do with livedb vs testdb
ran on win98 config...bombed out as before.

Compared C/ODBC setup on 2 machines. Noticed I had typed 300 in win2k DBMS cache, whereas win98 had 3000 (win98 has more mem than win2k)

So, I tried 300 on win98 on livedb, it worked!

So far I had run report getting data for a period covering 1 day.
So ran on both machines for a 2-3 week period, worked on win2k, not win98

I tried blank in both C/ODBC setups for DBMS cahce settings, just to see what happens. Worked fine in live-win2k, not at all on win98 (live or test)

So compared C/ODBC setups again (typed something diff somewhere?). Well yes, on the machine that worked, the win2k, I had typed "database.db" (not a typo here -> I actually had .DB and not .FDB) for name of database! I had the name of the database wrong in the setup that worked.

So, thought what the heck try it in win98, did not work.

Switched win2k to read database.fdb and ran on a couple of date ranges, all worked.

So, then I went the ADO2.1 to ADO2.0 route, on testdb on win98, Did not work at all on ADO2.1
It worked once on testdb-win98 with ADO2.0 and then nothing.

Got same error messages noted previously in this thread. After each error, shutdown & reboot win98 machine before trying again/something else.

So:
incorrect db name in C/ODBC setup in win2k, does not cause error.
varying DBMS cache does not cause error.

On win98 side, get it to run once - on 1 day report range.
Got it to run once on ADO2.0
Then nothing.

Interim conclusion: NF and/or C/ODBC is totally flakey - unreliable, unbelievable.

Tomorrow:
Try various configs on another win98 box to see if diff box makes difference.
Try testdb on win2k box to see what that does.
Try win2k on the win98 box I have been using - what the heck.
Try on a winXP box.

Need to be able to run stuff reliably on win98, 2k & XP machines as that is what we have.

Try using DAO.

Anybody know why the 16384 line limit using SQL noted in first post?
Any other C/ODBC driver from NF available (have 2.10.00.10)?
Open source driver?

MLW222

I can't get DAO to work, please help.

I have tried several dozen variations on the following example & other methods.
Code snippet:

Dim WrkODBC As Workspace
Dim Connect1 As Connection
Dim TableRecords As Recordset

Set WrkODBC = CreateWorkspace("", "", "", dbUseODBC)
Set Connect1 = WrkODBC.OpenConnection("", dbDriverPrompt, , "")

Set TableRecords = Connect1.OpenRecordset(SQL)

At "Set TableRecords =..."
Always get same error:
run-time error 3669
Execution cancelled

Can someone 'splain' why?
Can someone give me example DAO code that works?

PS. hunted all over web & help & MS to obtain examples, tried dozens as I said, always bombs out at set tablerecords = ...

MLW222

Well, got the report to work using ADO
Originally had it loop thru the 4 tables, and with each loop open & close a connection.
Rewrote so it only opens & closes connection once.

Still have DAO openrecordset question & sql 16384 line limit question.