ACC2000: need updateable Linked ODBC Foxpro tables

[Glenn]

Hi all,

With the arrival of a new machine, I decided to finally convert an old
Access97 database to the Access 2000 model.
on the previous machine I had access 97 & 2000 together just to able to
still use that mdb which uses linked FoxPro tables.

So, out with 97, all on 2000. (Access 2000SP3, Jet SP8, foxpro odbc
v6.01.8629.01)

At startup, the mdb relinks the tables to reflect the startup folder.
(the same mdb is reused as such for several different startup folders)

The tables themselves are part of a accounting program based on
(Visual?) FoxPro.

I successfully converted the relinked startup routine to use ODBC
connection.
The problem I have is with the indexes, necessary to get an updateable
table.
Most of the tables are uniquely identified by one field. But there are
some where two fields are needed.

When I add such a table manually, needing two fields as index, Access
shows me an updateable table.

When trying the same using code, the table is not updateable.
in my code (excerpt below) the index is created using SQL, as suggested
by several usenet postings.

example of a single-field SQL/index - resulting in updateable table
CREATE INDEX J0012004 ON J0012004([ldgnumber]) WITH PRIMARY

Two-field SQL/Index - resulting in non-updateable table
CREATE INDEX DOC2L ON DOC2L([docnumber],[linenumber]) WITH PRIMARY

A solution/hint would be very welcome. But please don't suggest I
reinstall Acc97 again

best regards
glenn
---------------
c_Connect = "ODBC;DSN=Visual FoxPro Tables;SourceDB=" & c_StartupDir _
& ";SourceType=DBF;Exclusive=No;BackgroundFetch=No;Collate=Machine;Null=
Yes;Deleted=No;DATABASE"

'WK_tables = table containing tablenames & hardcoded indexfields
Set rs = db.OpenRecordset("WK_Tables")
Set dbsODBC = OpenDatabase("", False, False, c_Connect)

Do While Not rs.EOF
c_fn = rs!name
Set v_index = rs!Index
With td_tbl
.Connect = dbsODBC.Connect
.SourceTableName = c_fn
End With
db.TableDefs.Append td_tbl
If Not IsNull(v_index) Then

c_SQL = "CREATE INDEX " & c_fn & " ON " & c_fn _
& "(" & v_index & ") WITH PRIMARY"
db.Execute c_SQL, dbFailOnError
End If
End If

rs.MoveNext
Loop
---------------

 

[Joe Fallon]

Try something like this in Access Queries:
CREATE UNIQUE INDEX Order2 ON OrderDetailsODBC (OrderID,OrderName);

 

[Glenn]

Try something like this in Access Queries:
CREATE UNIQUE INDEX Order2 ON OrderDetailsODBC (OrderID,OrderName);

Great, it works; all tables are updateable.
thank you very much for your help

best regards
glenn

 

[Joe Fallon]

Hurray!
I got one right. <g>

 

[Glenn]

Hurray!
I got one right. <g>

thanks,...

.... but as with so many things, I cheered too soon.
It seems that, with odbc, all fields are now set required (required
property = yes) whereas previously (Acc97-foxprolink) they were all set
as not required.

Is there an option to get back to not-required status ?
or will I have to modify all my sql statements?

best regards
glenn

 

[Glenn]

8<
(cautious) Yippee

I changed the options in the connect string as follows:
Exclusive=No;BackgroundFetch=No;Collate=Machine;Null=No;Deleted=NO

first it didn't work ?! but at the second attempt, bingo!

till next time
glenn

 

[Glenn]

Hurray!
I got one right. <g>

a final update:
despite my efforts and your help, I give in.
I've just seen the next thing that used to work,but no longer...
one form used to update the dataset in another form using filters. with
the odbc version, the dataset gets updated, but shows totally unrelated
records.
instead of losing more time to get it to work as it used to, I'll
install Access97 once again.
Perhaps when access 2010 comes along, I'll give it another shot...

bye
glenn

 

[Joe Fallon]

The move from A97 to A2000 was not made by many developers.
Things like changing the way Foxpro tables were handled were just one of the
many reasons.
A2003 is worth moving to (in general).
But maybe not for Foxpro.

I keep A97, 2000, 2002 and 2003 all installed.
Good luck!

 

[Glenn]

The move from A97 to A2000 was not made by many developers.
Things like changing the way Foxpro tables were handled were just one of the
many reasons.
A2003 is worth moving to (in general).
But maybe not for Foxpro.

I keep A97, 2000, 2002 and 2003 all installed.
Good luck!

yes, something to look forward too

thank you for your assistance.
glenn