Linking dBase IV and Sharing

[B Nieds]

Hi:

In a Access database I have links to dBase IV tables. I have then created
some queries and reports to present the data in the requested form. All the
simple stuff works but when I need to do some juggling then Access complains
that the table is in use by another user. Well that user is Access. The
symptoms reminded me of needing the old DOS SHARE program. Anyone have ideas
as to why Access will not share with itself and is there any settings I can
change to tell Access to share?

Example of the problem is:
I have set up a report which lists the data and then at the end of the
report to summerize the data using two subreports. The subreports run a
query which use the table that the report uses. The first subreport works
and the second fails because the file is in use by another user. Solution:
Have the second subreport query do a make table and then use the new table.

A second example is creating a query that uses a query to get the desired
data in the form needed (similar to help examples) but Access says the file
is in use by another user.

I am thinking that there is a setting in the registry or some INF file I
need to set/change but I have been unable to locate anything.

I would appreciate any comments.

At the office is XP Pro and at home Win98 2ndEd using Access 2000 with
latest patch applied. Same problem on both. Files can be on network or on
local computer with same results. No dBase index used.

Thanks
Bill

 

[Joe Fallon]

I converted off of dBase files 7 years ago.
Any reason not to just use Access?

I would consider using a linked mdb to hold temp tables and stuff. (keep the
growth of the main file to a minimum)
Then I would write queries that extract data once from dBase and toos the
data into Access.
Then base reports of the Access tables.

 

[B Nieds]

Hi Joe:

Thanks for the reply. There is a plan late this year or begining next year
that your plan will be implemented. For now however the dBase files are
maintained outside of my control.

In the meantime I need to run various reports on the data. I know I can
import the data but that then represents a fixed picture and would require
me to import several files several times before each report runs. (Time
consuming even if I automate the task.)

Do you know if when linking Access is restricted to exclusive use?

Thanks

Bill

 

[Joe Fallon]

It has been a long time.
I recal linking to dBase files and they worked with both Acces and dBase.
(Or I was lucky and the dBase users were never in when I was!)

Not sure if Unique Record Identifiers (fake indexes in Access) are required
or not.
That usually solves the Read Only problem.

This is the tip for it:

You can create a "Fake" index in Access that tells Access which columns make
a Unique record in the linked table. The index is only used by Access is
completely unknown to the Server.

The following example creates an index on an ODBC linked table. The table's
remote database is unaware of and unaffected by the new index. The following
example won't work if you simply paste it into Microsoft Access; you first
need to create an ODBC linked table called OrderDetailsODBC.

(CREATE INDEX Statement in Help has some examples)

CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID);

 

[B Nieds]

Hi Joe:

If I understand you correctly then I need to define a data definition query
to create an index for the affected tables. If this is correct (and if I set
it up correctly) then Access is unhappy and says "Cannot execute a data
definition statements on a linked data source". Any other thoughts?

Thanks
Bill

 

[Joe Fallon]

It is not a Data Definition query.
It is just a regular Access Query.
It creates an internal index in Access.
It does not affect the linked table in any way.
Try it again.

 

[B Nieds]

Hi Joe:

Ok so how do I tell Access? When I look in the help and when I read the
manual (yes I really do do that) and when I read some of the extra books I
have they all talk about Data Definition Queries for the Create Index
statement.

I created a new query and did not add any objects. I went to SQL (which says
Select; only) and I replace the Select; with "Create Index PHSCDE on PHSDATA
(PHSID);" and then click Run. Access immediately turns the query into a
Data Definition Query and the error message returns. I see nothing in the
books or help about adding any other SQL statements so I do not know what to
add to not create the Data Definition Query.

The help (and books) talk about defining a ODBC driver. Do I need to create
a ODBC driver to access the dBase IV files?

Thanks
Bill

 

[Joe Fallon]

Yep.
My bad.
That stuff works for ODBC linked tables.
Told you I haven't played with dBase in many years! <g>

As a test, try something completely different:
Copy the dBase files to a new folder.
ONLY the actual .dbf tables - no index files!
Then delete your links and try again.

Any luck?
(I used to do this with Foxpro files - Access didn't like dealing with the
index.)

Good luck.

 

[B Nieds]

Hi Joe:

Nada: I removed all indexes and relinked with no change. I am starting to
wonder if this is a problem with working with dBase or a general problem
working with any linked files or maybe this version of Access.

Thanks for all your suggestions so far.
Bill

 

[Davis]

Hi Joe:

Nada: I removed all indexes and relinked with no change. I am starting to
wonder if this is a problem with working with dBase or a general problem
working with any linked files or maybe this version of Access.

Thanks for all your suggestions so far.
Bill

Our new tool Super*SQL will produce regular or HTML reports directly
from your dBase files, or you could also use it to completely automate
the recreation of your dBase tables in Access using a "batch" script.
That way you could integrate the dBase and Access data into Access
reports.

We are in Phase II Beta testing now and if you wanted to get involved
you would get a free copy of the software.

For more information visit www.sqlmagic.com