dBASE Questions and Answers - Access Table problem

Author

Access Table problem

Ken Mayer [dBVIPS]

2006-12-08, 7:20 pm

This is a truly weird one.

I have a client with an Access database that I need to be able to read
in dBASE.

However, once the proper database/ODBC settings have been established,
in dBASE (a database object created, pointing to the correct database, a
query object created pointing to the table in question), the table is
always at endOfSet. (I can read other tables in the database, however)

? qFrom.rowset.endOfSet
Always returns true
? qFrom.rowset.first() // returns false
? qFrom.rowset.last() // returns false

Weirder:

? qFrom.rowset.count()
returns a number of 298 or whatever the number of records is

I can open the database and table in Access with no problem. But, I
cannot see what the error is.

Anyone with more knowledge of Access able to tell me what might be
causing this?

Thanks --

Ken

--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/dbase/dBASEBook.htm
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase

John Marshall

2006-12-08, 7:20 pm

Ken:

I personally was unable to get the ODBC to work, but have had fine luck with the Native driver MSACCESS (Access 97). I had to create it using the duflp BDEAlias.cc however, because doing it directly resulted in a password error.

Roland was instrumental in helping me figure this out.

Good Luck,

JM

Ken Mayer [dBVIPS] Wrote:

> This is a truly weird one.
>
> I have a client with an Access database that I need to be able to read
> in dBASE.
>
> However, once the proper database/ODBC settings have been established,
> in dBASE (a database object created, pointing to the correct database, a
> query object created pointing to the table in question), the table is
> always at endOfSet. (I can read other tables in the database, however)
>
> ? qFrom.rowset.endOfSet
> Always returns true
> ? qFrom.rowset.first() // returns false
> ? qFrom.rowset.last() // returns false
>
> Weirder:
>
> ? qFrom.rowset.count()
> returns a number of 298 or whatever the number of records is
>
> I can open the database and table in Access with no problem. But, I
> cannot see what the error is.
>
> Anyone with more knowledge of Access able to tell me what might be
> causing this?
>
> Thanks --
>
> Ken
>
> --
> /(Opinions expressed are purely my own, not those of dataBased
> Intelligence, Inc.)/
>
> *Ken Mayer* [dBVIPS]
> /Golden Stag Productions/
> dBASE at goldenstag dot net
> http://www.goldenstag.net/dbase/dBASEBook.htm
> http://www.goldenstag.net/GSP
> http://www.goldenstag.net/dbase

Ken Mayer [dBVIPS]

2006-12-08, 7:20 pm

John Marshall wrote:
> Ken:
>
> I personally was unable to get the ODBC to work, but have had fine luck with the Native driver MSACCESS (Access 97). I had to create it using the duflp BDEAlias.cc however, because doing it directly resulted in a password error.
>
> Roland was instrumental in helping me figure this out.
>

I have the ODBC stuff working. The problem is this *one* table, that
doesn't work. I can view other tables in dBASE, I can read the data, but
this one table is causing me problems.

Ken

--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/dbase/dBASEBook.htm
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase

Roland Wingerter

2006-12-08, 7:20 pm

Ken Mayer [dBVIPS] wrote

> This is a truly weird one.
>
> I have a client with an Access database that I need to be able to read in
> dBASE.
>
> However, once the proper database/ODBC settings have been established, in
> dBASE (a database object created, pointing to the correct database, a
> query object created pointing to the table in question), the table is
> always at endOfSet. (I can read other tables in the database, however)
>
> ? qFrom.rowset.endOfSet
> Always returns true
> ? qFrom.rowset.first() // returns false
> ? qFrom.rowset.last() // returns false
>
> Weirder:
>
> ? qFrom.rowset.count()
> returns a number of 298 or whatever the number of records is
>
> I can open the database and table in Access with no problem. But, I
> cannot see what the error is.
-------
Just guessing here...

Does the table have any special fieldtypes that could cause problems?
Maybe more fields than dBASE can handle?

Roland

Ken Mayer [dBVIPS]

2006-12-08, 7:20 pm

Roland Wingerter wrote:
> Ken Mayer [dBVIPS] wrote
>
>
> -------
> Just guessing here...
>
> Does the table have any special fieldtypes that could cause problems?
> Maybe more fields than dBASE can handle?

Hmm. Number of fields doesn't seem to be a problem. I suppose it's
possible there's a field type that dBASE doesn't recognize (hmm --
doesn't *appear* to be - it's all Text, Date/Time and Number in design
view for Access). We did find a workaround, and that is that the table
can be saved out to an Excel spreadsheet, and the import for that works
fine. It is disconcerting though.

Ken

--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/dbase/dBASEBook.htm
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase

Roland Wingerter

2006-12-08, 7:20 pm

Ken Mayer [dBVIPS] wrote

>
> Hmm. Number of fields doesn't seem to be a problem. I suppose it's
> possible there's a field type that dBASE doesn't recognize (hmm --
> doesn't *appear* to be - it's all Text, Date/Time and Number in design
> view for Access). We did find a workaround, and that is that the table can
> be saved out to an Excel spreadsheet, and the import for that works fine.
> It is disconcerting though.
------
I am not sure, but I think I had the same problem once, but it was not
important at the time.

Roland

Ken Mayer [dBVIPS]

2006-12-08, 7:20 pm

Roland Wingerter wrote:
> Ken Mayer [dBVIPS] wrote
>
>
> ------
> I am not sure, but I think I had the same problem once, but it was not
> important at the time.

Weird. I figure there's probably either some switch in the table
somewhere that forces some filter, or ... er ... well, it's Access, I
dunno. At least there's a way to get the data, so the client isn't
hosed, but it's weird.

Ken

--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/dbase/dBASEBook.htm
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase

Eric Logan

2006-12-08, 7:20 pm

Ken;
I have had good results using Updateset() to append Access data through ODBC
into a DBF.
E.L.

David Kerber

2006-12-11, 7:17 pm

In article <Wh0udWwGHHA.1332@news-server>, dbase@_nospam_golden
stag.net
says...
> Roland Wingerter wrote:
>
> Hmm. Number of fields doesn't seem to be a problem. I suppose it's
> possible there's a field type that dBASE doesn't recognize (hmm --
> doesn't *appear* to be - it's all Text, Date/Time and Number in design
> view for Access). We did find a workaround, and that is that the table
> can be saved out to an Excel spreadsheet, and the import for that works
> fine. It is disconcerting though.

Those string fields aren't unicode, are they? The BDE doesn't speak
unicode at all.

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Ken Mayer [dBVIPS]

2006-12-11, 7:17 pm

Eric Logan wrote:
> Ken;
> I have had good results using Updateset() to append Access data through ODBC
> into a DBF.

That only helps if the table structures are identical, which in this
case you can't count on ...

Ken

--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/dbase/dBASEBook.htm
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase

Ken Mayer [dBVIPS]

2006-12-11, 7:17 pm

David Kerber wrote:
> In article <Wh0udWwGHHA.1332@news-server>, dbase@_nospam_golden
stag.net
> says...
>
>
>
> Those string fields aren't unicode, are they? The BDE doesn't speak
> unicode at all.

Don't appear to be -- they're from an American firm, and appear to be
standard English, and all that.

Ken

--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/dbase/dBASEBook.htm
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase

Roland Wingerter

2006-12-11, 7:17 pm

David Kerber wrote

>
> Those string fields aren't unicode, are they? The BDE doesn't speak
> unicode at all.
-------
That could be an explanation. The field type of Unicode strings is nvarChar.

As a test, I tried to query a table in MS SQL Express from dBASE. The table
is in the AdventureWorks database.

One of the fields is 'Title', field type nVarChar(8).

Here is what I tried:

db = new database(" Adventureworks_clien
t")
db.active := true

q1 = new query()
q1.database := db
q1.sql := "select cast(Title as character(20)) as mytitle from
Person.Contact"
q1.active := true // Database Engine Error: Capability not supported

However, this works:

SQLExec("select cast(Title as character(8)) as mytitle from
Person.Contact","result")
USE result
BROWSE

Hope this helps

Roland

Ken Mayer [dBVIPS]

2006-12-11, 7:17 pm

Roland Wingerter wrote:
> David Kerber wrote
>
>
> -------
> That could be an explanation. The field type of Unicode strings is nvarChar.
>
> As a test, I tried to query a table in MS SQL Express from dBASE. The table
> is in the AdventureWorks database.
>
> One of the fields is 'Title', field type nVarChar(8).
>
> Here is what I tried:
>
> db = new database(" Adventureworks_clien
t")
> db.active := true
>
> q1 = new query()
> q1.database := db
> q1.sql := "select cast(Title as character(20)) as mytitle from
> Person.Contact"
> q1.active := true // Database Engine Error: Capability not supported
>
> However, this works:
>
> SQLExec("select cast(Title as character(8)) as mytitle from
> Person.Contact","result")
> USE result
> BROWSE
>
> Hope this helps

Nope. Not the problem. I can get the active property of the query to
work. I can get the rowcount of the rowset with
queryname.rowset.count(), but I can't navigate.

Everything works up to the point where I try to navigate or read data:

? queryname.rowset.first() // false
? queryname.rowset.last() // false
? queryname.rowset.endOfSet // true

For some reason it is always at the end of set, and I cannot move off
it, even though count() returns 299 (or whatever).

Ken

--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/dbase/dBASEBook.htm
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase

Eric Logan

2006-12-11, 7:17 pm

> That only helps if the table structures are identical, which in this
> case you can't count on ...
> Ken

Doesn't need to be identical. It is quite flexible.
(Access 2000 database, ODBC-BDE connection,
Updateset(), Append into a pre-build DBF.)
For example, I just appended data from an Access table with this structure:
1 Date TIMESTAMP 19
2 Trap VARCHAR 50
3 Code VARCHAR 50
4 FC VARCHAR 50
5 Spp VARCHAR 50
6 Color VARCHAR 50
7 Fin Marked VARCHAR 50
8 Total Marked DOUBLE 15
9 RDate TIMESTAMP 19
10 JulianWeeks SMALLINT 5
11 Site VARCHAR 4

Into a dbase table with this structure:

1 DT DATE 8
2 TR CHARACTER 8
3 CD CHARACTER 8
4 FC CHARACTER 4
5 SP NUMERIC 3
6 CO CHARACTER 15
7 FM CHARACTER 15
8 TM NUMERIC 5
9 RD DATE 8
10 JW NUMERIC 2
11 ST CHARACTER 10
E.L.

Ken Mayer [dBVIPS]

2006-12-12, 7:15 pm

Eric Logan wrote:
>
>
> Doesn't need to be identical. It is quite flexible.
> (Access 2000 database, ODBC-BDE connection,
> Updateset(), Append into a pre-build DBF.)

What I mean is that the fields have to be in the same order, and all
that, which in this case is not an option (the program I was working on
is one for a "generic" append of data from the Access table to a
pre-defined dBF, with field mapping, similar to the way
:dUFLP:ImportData.wfm works). And I have a feeling that the UpdateSet
object would have the same problems with the data that regular OODML
commands do -- the endOfSet issue.

Ken

--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/dbase/dBASEBook.htm
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase

Roland Wingerter

2006-12-12, 7:15 pm

Ken Mayer [dBVIPS] wrote

>
> Nope. Not the problem. I can get the active property of the query to work.
------
Yes, I see.

Roland

Sponsored Links