how to update SQL table from FoxPro cursor 2

[campagnolo1]

Hello everyone,

I've been chasing my tail here for a while and could use some help. I have a FoxPro exe that runs a bunch of queries and puts them in a cursor. In the end the exe copies the final cursor into a dbf but I want it now to put the data into a SQL table. I created the SQL table with the exact columns and data types that the cursor has. But I'm having issues on getting the data into the SQL table. I have tried this:

Select C5.*, bmsl.findno, bmsl.scrpad;
	FROM C5 Left Join bmsl On C5.fg + C5.partno + C5.sorev = bmsl.itemparent + bmsl.itemchild + bmsl.rev;
	INTO Cursor C6
	
connstr = SQLSTRINGCONNECT("Driver={SQL Server}; Server=KOKATAT-507BB64\SQLExpress; Database=WebPortal;Trusted Connection=Yes")
execmd = "INSERT INTO itemprojimstock (Item) VALUES (?'test')"

	?SQLEXEC(connstr, execmd)
	SQLDISCONNECT(connstr)

CLOSE ALL

and this works but of course puts the value "test" in the column and not the actual cursor value. If I try something like this:

execmd = "INSERT INTO itemprojimstock (Item) FROM C6 SELECT item WHERE C6.item <> ('')"

I get a -1 and nothing gets entered in the database.
What syntax do I need to use to get all values from my cursor into the sql table?

Thanks a lot in advance for the help!

Cheers,

Chris

 

[Mike Lewis]

I mentioned Bulk Insert much earlier in this thread, but I didn't follow it up because it is specific to SQL Server, and Chris has not yet told us which back-end he is using. Chris, if you could clarify that point, it would be helpful.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[campagnolo1]

Good morning gentlemen,

I mentioned Bulk Insert much earlier in this thread, but I didn't follow it up because it is specific to SQL Server, and Chris has not yet told us which back-end he is using. Chris, if you could clarify that point, it would be helpful.

Mike, currently everything (accounting and manufacturing software) is using FoxPro dbf free table directories. We are creating custom tables for a few of our Crystal reports that would otherwise either not be able to run or would take forever to run. I'm now in the process of creating a web portal for all of our reports using Visual Studio. The reason for that is that the reports with custom tables need to use an xBase connector so that the custom tables can be refreshed by any user at any time (which doesn't work with ODBC). That in return requires that we have two seperate versions of report viewer (DataLink Viewer 9 and 2011). We probably could make do with only DataLink Viewer 9 (the older version) but like some of the funtionality of DLV 2011. And since the software company now has just released the accounting and manufacturing software with a SQL backend. So that's why I'm trying to use SQL (which is SQL Server 2008 R2).
Clear as mud?

Chris

 

[Mike Lewis]

Chris,

Clear as mud?

Actually, no - and not particularly relevant. But never mind. I might have a better solution for you.

You say that the xBase connector in CR let's you access a table without opening it exclusively, but that you had an error when you tried to use it with a VFP table. I've just been experimenting with the xBase connector. I can get it to open VFP tables correctly, provided these are saved in the "old" (pre-visual) format.

With that in mind, you should be able to report on your original data, without any need to upload it to another database, as follows:

First, create the cursor as before, and then copy it to a Fox2x file:

Select C5.*, bmsl.findno, bmsl.scrpad;
	FROM C5 Left Join bmsl On C5.fg + C5.partno + C5.sorev = bmsl.itemparent + bmsl.itemchild + bmsl.rev;
	INTO cursor C6
select C6
COPY TO C6_X TYPE FOX2X

Then create a report in CR, using the xBase connector to open C6_X.DBF.

This will give you all the required data, in the form that you need it for the report, without the shared-access issue.

There are a couple of caveats:

1. If the original tables contains long field names, these will be truncated in the report. That won't matter, provided the truncated versions are still meaningful.

2. Although mulitple CR users will be able to share the table, you won't be able to overwrite it with a new version (with the same name) while any of the reports is open. If you try, the COPY command will give a "file in use" error. One workaround might be not to overwrite it with new data, but to delete all the existing records, and then append the new records to it, with an occasional PACK when convenient.

I think you will find this method considerably faster than any of the other methods we have discussed.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[campagnolo1]

Mike,

Then create a report in CR, using the xBase connector to open C6_X.DBF.

We would like to move away from the xBase connector since we are trying to use DataLink Viewer 2008 and Crystal Reports Viewer 2008 which gives you a Database Connection Error when using xBase. The way you have described it in your post is actually the way we've had it for a few years now, but that required using the older viewer software.

You say that the xBase connector in CR let's you access a table without opening it exclusively, but that you had an error when you tried to use it with a VFP table. I've just been experimenting with the xBase connector. I can get it to open VFP tables correctly, provided these are saved in the "old" (pre-visual) format.

The error occurs when I use the newer viewer software (DLV 2008) and not when I try to oopen a VFP table.

So to make a long story short, I would like to move away from DataLink Viewer and use a browser based portal that uses Crystal Reports Viewer 2008 for viewing the reports. That requires either OLE DB or ODBC connectors because the CRV 2008 does not work with xBase. But using OLE DB or ODBC will put a lock on the table and I'm not able to refresh the table at-will if somebody else is viewing the report. So I thought that putting the data into a SQL table might be the best solution.

I'm sorry if I'm repeating myself, I know it can be hard trying to convey something in writing, especially if english is your second language (which is the case for me). I really appreciate your guys' help and so far I've already learned a lot!

Now to Olaf's code from earlier on:
1. There is no ID field, I set the Item field as the primary key.

SQLEXEC(connstr,"SELECT * FROM itemprojimstock WHERE 1=0","curAppend")	 
SET MULTILOCKS ON
CURSORSETPROP('Buffering',5,'curAppend')
CURSORSETPROP('Sendupdates',.T.,'curAppend')
CURSORSETPROP('Tables','itemprojimstock','curAppend')
CURSORSETPROP('KeyFieldList', 'Item','curAppend') && important: is ID really the name of a primary key field of the itemprojimstock table? Is such a field missing?
CURSORSETPROP('UpdatableFieldList','Item, Sono, Sorev, Sqty, Needdate','curAppend') && what about Sorev? Further fields? What about the ID field? I said you have to specify the key field here, too
CURSORSETPROP('UpdateNameList','Item Item,Sono Sono, Sorev Sorev, Sqty Sqty, Needdate Needdate','curAppend') && because cursor field names and sql table field names are the same, this looks odd, but needs to be this way, I said so.
SELECT curAppend
APPEND FROM DBF("C6")
TABLEUPDATE(2,.T.,"curAppend")
CLOSE ALL

2. The above code works great, but I think there is a caveat that I think I forgot about:
a. the table holds work order information, and each time the data gets refreshed there is (mostly) all new information. If I understand the
code correctly, it keeps the primary key field and updates all the other fields, correct?
If that is the case, then tableupdate() may not work since I basically need the table to be cleared and the new data entered each time the code
runs.

Thanks,

Chris

 

[Mike Lewis]

Chris,

1. Are you sure the xBase problem is related to Data Link Viewe? As far as I know, Data Link Viewer just uses Crystal Reports as a COM object; it doesn't have any functionality of its own.

2. You say you are "not able to refresh the table at-will if somebody else is viewing the report". I still think that's because of an incorrect setting in your OLE DB setup. I'm sorry I can't give you more details, because I don't have access to that system at the moment.

3. If by "refresh the table", you mean overwrite it with entirely new contents, you will never be able to do that. You would in effect be deleting it and recreating it, and you can't delete a file (any file) while another application has it open. That's why I suggested you deleted the individual records instead, and then append the new ones.

Re point 2, if someone else here can tell Chris how to configure the Foxpro OLE DB driver so that it does not take exclusive use of the table, that might solve his problem. I know that you can do that with the ODBC driver (it's in the same dialogue as collating sequence, null support, etc.). But I'm not sure about OLE DB.

Olaf? JRB-Bldr? Anyone?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[campagnolo1]

Mike,

is this what you are talking about?
How to configure an OLE DB driver

Chris

 

[campagnolo1]

So,

I've been looking at the tutorial for configuring the OLE DB datasource, unfortunately without success.
I set up two datasources, one OLE DB and one ODBC, and configured the report to use them (one at a time of course).
Both times I ran the report and then tried to run the VFP exe, both times getting the "File Access is denied" error.

Here is the OLE DB data connector:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=VFPOLEDB.1;Data Source=C:\Temp\itemprojimstock.dbf;Mode=Share Deny None;Password="";Collating Sequence=MACHINE;DELETED=True;MVCOUNT=24000;ENGINEBEHAVIOR=90;TABLEVALIDATE=0;REFRESH=5;REPROCESS=5

and the ODBC connector (yes, it is OLE DB but uses an ODBC DSN)

[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;Data Source=itemprojimstock;Mode=Share Deny None

I have tried changing the value for the Mode option, but it doesn't seem to make a difference.

Chris

 

[Mike Lewis]

Unfortunately, ODBC doesn't work with tables created with the latest versions of VFP. The VFP ODBC driver hasn't been updated since ver 6.0, and so will only work if you created your tables with an earlier version (or if you saved them as TYPE FOX2X, as I described earlier). That's why you need to use OLE DB instead.

Regarding the "File access denied" error, is it your VFP EXE or Crystal Reports that is giving that message? Earlier, I assumed it was CR, which is why I suggested it was an OLE DB configuration issue. But if the message is coming from the EXE, then it's likely that the EXE is trying to open the table exclusively, which it cannot do because it is already opened within Crystal.

If that's the case, the solution would be to modify the VFP EXE so that it does not open the table exclusively - assuming that's possible. Just to be clear, the table in question is the one that you are linking to within CR. It's got nothing to do with the C6 cursor that you created in your SQL uploading code. In fact, there are probably several tables involved; they must all be opened non-exclusively.

It could be that the VFP app needs exclusive use so it can perform maintenance, such as a pack or a zap. If so, you might need to change the app's logic a little. But I suggest you at least try opening the tables non-exclusively to see if it solves the "file access denied" problem. If it does, you can worry about how to deal with packing or whatever later.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

>The above code works great, but I think there is a caveat that I think I forgot about:
>a. the table holds work order information, and each time the data gets refreshed there is (mostly) all new information. If I understand the
>code correctly, it keeps the primary key field and updates all the other fields, correct?
Only halfways.

The code itself inserts new records, doesn't it? So indeed it answers your question with NO.

TableUPDATE() is the name of the function, but it also inserts and deletes, it does commit all changes in the buffer of an alias. Think of it as BufferCommit()

You are right the reason you have to specify a keyfield is mainly for updates and deletes of records. For inserts it's not always necessary to know a primary key field, nevertheless Tableupdate() will not work without that cursor property. In updates and deletes VFP generates it uses a WHERE id=? clause and sends these queries to whatever remote backend or DBFs, for which this also works. But also in case the primary field of a table is autogenerated and therefore a readonly field of a table, it's important for foxpro to know it has to skip this field in inserts it sends to the remote database.

It may seem weird to first select no record into a cursor, to then insert records and send them to the database, but the main advantage is, you only need the one tableupdate() to push in all data. The query on the remote backend is the equivalent of USE someview with NODATA. You get a cursor representing the remote table to work on, as if it was a DBF.

The number of commands of my code may be larger, perhaps than with the prepared statement, and you don't get around specifying fieldname lists, etc., but you don't scan each single record you want to insert. The main number of commands don't take many time, cursorsetprops are instantly done. You can generalize much of the code using AFIELDS() on the empty cursor you get from the SQLExec of the WHERE 1=0 query. And so it's not as you think mainly done for editing existing data. No. TABLEUPDATE() is the core VFP code for persisting data via such SQLEXec cursors, via updatable local or remote views, via cursoradapter and you can even use it with DBFs, so it's the thing function working for everything.

Bye, Olaf.

 

[Olaf Doschke]

>since I basically need the table to be cleared and the new data entered each time the code
And to accomplish that simply send a SQLEXEC(connstr,"TRUNCATE itemprojimstock") before using the code and you start on an emtied SQL Server table. You need this with the SQLPreprare() and SQLExec() of single Inserts too, as this code also only adds to the table and doesn't clear it before.

And another way to do it would be using CREATE TABLE and finish with DROP TABLE. Or you work with Temp Tables in SQL Server.

Bye, Olaf.

 

[Olaf Doschke]

Oh, and regarding your configurations. Both in OLEDB and ODBC you never specify a single DBF as Source, either you specify a DBC, but as you only have free fox2x tables you specify the directory only. The mode of the driver is either database or directory of free tables, it's never a single DBF.

Bye, Olaf.

 

[Mike Lewis]

[oledb]
; Everything after this line is an OLE DB initstring
Provider=VFPOLEDB.1;[b]Data Source=C:\Temp\itemprojimstock.dbf[/b];
Mode=Share Deny None;Password="";Collating Sequence=MACHINE;DELETED=True;
MVCOUNT=24000;ENGINEBEHAVIOR=90;TABLEVALIDATE=0;REFRESH=5;REPROCESS=5

Also, it's not a good idea to use C:\Temp as the data source. You have no control over what other files might be present there, or how long your own files are likely to stay there.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, I disagree slightly about Temp folder usage. VFP uses it for cursors and disposes them automatically, but that doesn't has to be that way, you can store something there temporarily, you should expect the temp folder to be cleared by a system reboot, but otherwise you can even use it to share data with several processes on the local machine. It's intended for that usage, isn't it? So mostly in regard of the likeliness of files to stay there, that's up to you, unless you need it more than on the currentsession, the current reprt output. The OS let's any process and any user write there and it's not an automatic trash folder. To get no overlap in usage of the same name you can simply check file existence, you can also use random names, as given by SYS(2015). And as the folder is local, you would only have a problem, if a user does two similar reports at the same time.

What's true: A separate folder for your temporary data would give you a clean overview as developer or admin in case you are asked to debug something, for example, the OS temp folder is shared with anything wanting to write a temp file.

Bye, Olaf.

 

[Mike Lewis]

I disagree slightly about Temp folder usage

Fair comment. I don't feel strongly about it, and it's not central to the original problem.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[campagnolo1]

Mike and Olaf,

great explanations and very useful information!

Regarding the "File access denied" error, is it your VFP EXE or Crystal Reports that is giving that message? Earlier, I assumed it was CR, which is why I suggested it was an OLE DB configuration issue. But if the message is coming from the EXE, then it's likely that the EXE is trying to open the table exclusively, which it cannot do because it is already opened within Crystal.

I never posted the full code, otherwise you would have seen that I'm opening all needed tables non-exclusive:

Close All
Set Exclusive Off
Use F:\TIW\KOKAC\immaster In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKAC\imstock In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Somater In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\soheader In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Bmrev In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\bmsl In 0
Set TABLEVALIDATE To 0
Use F:\TIW\KOKWS\Soroute In 0
Set TABLEVALIDATE To 0

**If File("C:\Temp\itemprojimstock.DBF" )
**Delete File ("C:\Temp\itemprojimstock.DBF")
**ENDIF

connstr = SQLSTRINGCONNECT("Driver={SQL Server}; Server=KOKATAT-507BB64\SQLExpress; Database=WebPortal;Trusted Connection=Yes")

Select soheader.partno As Item,;
	soheader.sono As sono,;
	soheader.rev As sorev,;
	soheader.sqty As sqty,;
	soheader.need_date As needdate,;
	soheader.priority,;
	soheader.salesno,;
	soheader.crea_date,;
	soheader.start_date,;
	soheader.remark1,;
	soheader.remark2,;
	soheader.instr1,;
	soheader.instr2,;
	soheader.plandate,;
	soheader.rev,;
	soheader.fgloc,;
	soheader.mtlloc,;
	soheader.solineno,;
	soheader.Userid,;
	soheader.part_desc,;
	Soroute.opno As routeopno,;
	Soroute.loadcenter,;
	Soroute.Descrip As Routedes,;
	immaster.misc04 As lottrack,;
	immaster.upccode As upccode;
	FROM soheader Inner Join Soroute On soheader.sono = Soroute.sono;
	LEFT Join immaster On soheader.partno = immaster.Item;
	Into Cursor c1

Select c1.*,;
	Somater.partno As partno,;
	STR(Asc(Somater.Phanref),3)As Phanref,;
	STR(Asc(Somater.Phanid),3)As Phanid,;
	VAL(Somater.qty_assy) As qty_assy,;
	VAL(Somater.qty_aloc) As qty_aloc,;
	Somater.Delmark As Delmark;
	FROM c1 Left Join Somater On c1.sono = Somater.sono And c1.routeopno = Somater.opno;
	INTO Cursor c2


Select c2.*,;
	immaster.Descrip As Descrip,;
	immaster.stockum As stockum,;
	immaster.misc04 As misc04,;
	immaster.lotstat As lotstat;
	FROM c2 Left Join immaster On c2.partno = immaster.Item;
	Into Cursor c3

Select c3.*,;
	imstock.lonhand As lotqty,;
	imstock.locid As lotloc,;
	imstock.lot As lotnum;
	FROM c3 Left Join imstock On imstock.Item = c3.partno AND c3.mtlloc = imstock.locid;
	Into Cursor c4 

Select c4.*, Iif(Empty(Bmrev.fgparent), Bmrev.itemparent, Bmrev.fgparent ) As fg;
	FROM c4 Left Join Bmrev On c4.Item + c4.sorev = Bmrev.itemparent + Bmrev.rev;
	into Cursor C5

Select C5.*, bmsl.findno, bmsl.scrpad;
	FROM C5 Left Join bmsl On C5.fg + C5.partno + C5.sorev = bmsl.itemparent + bmsl.itemchild + bmsl.rev;
	INTO Cursor C6

And from there on I have now adopted Olaf's code:

SELECT C6
lcExeCmd= "INSERT INTO itemprojimstock (Item, Sono, Sorev, Sqty, Needdate, Priority, Salesno, Crea_date, Start_date, Plandate, Rev, Fgloc, Mtlloc, solineno, userid, part_desc, routeopno, loadcenter, routedes, lottrack, upccode, partno, phanref, phanid, qty_assy, qty_aloc, delmark, descrip, stockum, misc04, lotstat, lotqty, lotloc, lotnum, fg, findno, scrpad) VALUES (?lcItem, ?lcSono, ?Sorev, ?lcSqty, ?lcNeeddate, ?lcPriority, ?lcSalesno, ?lcCrea_date, ?lcStart_date, ?lcPlandate, ?lcRev, ?lcFgloc, ?lcMtlloc, ?lcsolineno, ?lcuserid, ?lcpart_desc, ?lcrouteopno, ?lcloadcenter, ?lcroutedes, ?lclottrack, ?lcupccode, ?lcpartno, ?lcphanref, ?lcphanid, ?lcqty_assy, ?lcqty_aloc, ?lcdelmark, ?lcdescrip, ?lcstockum, ?lcmisc04, ?lclotstat, ?lclotqty, ?lclotloc, ?lclotnum, ?lcfg, ?lcfindno, ?lcscrpad)"
SQLPREPARE(connstr, lcExeCmd)
SCAN
lcItem = C6.Item
lcSono = C6.Sono
lcSorev = C6.Sorev
lcSqty = C6.Sqty
lcNeeddate = C6.Needdate
lcPriority = C6.Priority
lcSalesno = C6.Salesno
lcCrea_date = C6.Crea_date	
lcStart_date = C6.Start_date
lcRemark1 = C6.Remark1
lcRemark2 = C6.Remark2
lcInstr1 = C6.Instr1
lcInstr2 = C6.Instr2
lcPlandate = C6.Plandate
lcRev = C6.Rev
lcFgloc = C6.Fgloc
lcMtlloc = C6.Mtlloc
lcSolineno = C6.Solineno
lcUserid = C6.Userid
lcPart_desc = C6.Part_desc
lcRouteopno = C6.Routeopno
lcLoadcenter = C6.Loadcenter
lcRoutedes = C6.Routedes
lcLottrack = C6.Lottrack
lcUpccode = C6.Upccode
lcPartno = C6.Partno
lcPhanref = C6.Phanref
lcPhanid = C6.Phanid
lcQty_assy = C6.Qty_assy
lcQty_aloc = C6.Qty_aloc
lcDelmark = C6.Delmark
lcDescrip = C6.Descrip
lcStockum = C6.Stockum
lcMisc04 = C6.Misc04
lcLotstat = C6.Lotstat
lcLotqty = C6.Lotqty
lcLotloc = C6.Lotloc
lcLotnum = C6.Lotnum
lcFg = C6.Fg
lcFindno = C6.Findno
lcScrpad = C6.Scrpad
SQLEXEC(connstr)
ENDSCAN
SQLDISCONNECT(connstr)
CLOSE ALL

Mike, I'm not sure what it is, but whichever way I turn with my OLE DB connector, it just won't work. The scenario I'm looking at is that somebody opens the report in DataLink Viewer 2011 (I think earlier I mistakenly wrote 2008) and while the report is open, another user can run the VFP exe, which will delete and recreate the table, and then view the report with the new data. But as we know now, with OLE DB I can't delete the table.

Olaf, so far the code works and I will try your modification suggestion of using TRUNCATE. One strange thing I have encountered though is that I'm getting a "Command contains unrecognized phrase/keyword" error on the lcExeCmd="INSERT..." line which seems to be tied to certain fields. I'm still trying to figure them all out, but so far I know that REMARK1 and REMARK2 are causing problems. Both are character fields in the VFP table and SQL table. I will have to work more on that.

Cheers,

Chris

 

[Olaf Doschke]

In your line cExeCmd="INSERT..." the string literal is too long. You can only have 254 characters between the string delimiters. So you better put that in

[URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=1716941[/URL]
TEXT TO cExeCmd NOSHOW
INSERT...
ENDTEXT

Then you may also format the insert to a readable query, not an endless single line. SQL Server will acccept line breaks within a command, don't concatenate lines with semicolons, then! T-SQL only optionally expects semicolons at the end of commands.

Bye, Olaf.

 

[Olaf Doschke]

And indeed you could do more than one join per query, but I won't dive into that. If the data amount is limited for a report, it shouldn't matter much. It works and you may find it easier to maintain than a huge single query.

Bye, Olaf.

 

[Mike Lewis]

... while the report is open, another user can run the VFP exe, which will delete and recreate the table ...

Chris, that's exactly what I've been trying to say. If the table is open in the report, no other process can delete it. That's got nothing to do with Crystal Reports, or DataLink Viewer, or OLE DB, or even FoxPro. You simply can't delete a file - any file - while it's open.

That's why I suggested that, instead of deleting the physical table, you delete all the old records and append the new ones. If you could do that, the "file access denied" error will disappear. That's true regardless of what program or access method you are using to access the file for reporting.

That said, I see you are making some progress with Olaf's suggestion, so by all means stay with that instead.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[campagnolo1]

Mike,

how would I go about just deleting the records and appending? I appreciate your help and I'm trying to understand both ways, yours and Olaf's. I think ultimately I'm hoping to go with the SQL solution, but I also would like to get the VFP solution just so I have options.

And I can't say thank you enough to both of you for this awesome thread and you sharing your knowledge! I hope that other people will be able to benefit from this thread!

Chris

 

[Olaf Doschke]

I may answer in parallel to Mike: To delete records you can USE table Sahred and DELETE ALL. The problem then is not explusive access, but if the tables are still in use for a report, deleting data would effect the running report, wouldn't it?

Here generating random DBF names with SYS(2015) comes in handy, I just don't know how you could setup a crytal report to work on varying table names.

A solution might be to add a field into your dbf(s) with eg a reportnumber, Make it one parameter for Crystal to only read all records with a certain record number. Then you can run reports in parallel and would only need to delete data and pack th shrink the table. On e easy way would be to try to delete the files at the start of your exe, then this would happen about once a day when the first user enters. If you fail with deleting, that doesn't matter, just determine the next higher reportnumber to put into that seq number field and you seperate the report data for each user.

Bye, Olaf.