Script changes to a DBF file

[nelifecare]

I have a Visual FoxPro 3rd party app that uses a DBF file to store printer settings. I need to move some printer shares around and was wondering if I could script changes to each users individual printer.dbf file.

Any ideas?

 

[Mike Lewis]

Yes, this is easy. Just create a file containing ALTER TABLE commands (see the VFP Help for the syntax). Then, anytime you want to implement your changes, just run that file.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Mike Lewis]

Nelifecare,

Have just re-read your post, and think I might have misunderstood your request. I thought you wanted to change the structure of your DBFs. But perhaps you just want to change the actual data.

If so, the principle is the same, but, instead of ALTER TABLE commands, you would use UPDATE, INSERT or DELETE commands. Either way, you create a file that you can run any time.

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[nelifecare]

Hi Mike,

Thanks for the response. I need to be a bit more clear, how would I do this not knowing anything in regards to VFP?

I understand SQL etc, just how would I go about scripting this for VFP?

 

[Mike Lewis]

Nelifecare,

First, you would need a copy of Visual FoxPro (any recent version).

You would then create the script file containing the SQL commands (ALTER TABLE or UPDATE / INSERT / DELETE, as appropriate). You can use either VFP's internal editor, or any other text editor, to do that, provided you name the file with the extension PRG.

Then, you could run the file directly from VFP on your own system. Or you could compile it into a self-contained executable for your users to run.

There are some other complications. The script that you write would need some way of knowing the path to the tables that you want to update. It might also need to gain exclusive use of the tables (in other words, check that no other user is accessing the tables at the time).

Also, if you are sending a compiled version out to your users, there are several runtime library files that you need to worry about.

Obviously, it's difficult to give complete step-by-step instructons that would cover all the issues. At least, I hope this will help you understand what's possible.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[JRB-Bldr]

The first questions I would have is concerning this/these Printer DBF table(s)...

If there is only a single Printer DBF table, is there a separate file on each individual user's workstation?

If not, then are there individual Printer DBF tables for each user in some single common location?

If not, then are there individual records for each user within the single common Printer DBF table?

As you can see, the 3rd party app might have been created in a variety of ways so as to control printer configurations.

It might hold a single set of configuration settings for ALL users (not a good idea, but I have seen it done this way).

It might hold separate setting for each printer regardless of user.

It might hold separate settings for each user.

Unless it is OLD code, it should just use the Windows printer(s) which is/are configured into each user's workstation (but not always). In which case you might not need to change anything in the Printer DBF, but instead change the Windows printer settings.

Or things could be handled in some other manner.

In order to write code to change settings you first need to know where the settings 'live' and how they are stored.

Another issue might be to understand how the 3rd party app 'knows' what printer configuration to use.
* Does it 'know' the user and can change which configuration accordingly?
* Does it not care about the user, but instead 'knows' which printer is requested and uses that configuration data?

The code you write/modify might need to take that into consideration as well.

Obviously, since you are in a VFP forum, our first choice would be for you to learn some VFP code and do the job within VFP using the most simple method.

An alternate method would be to use SQL code and attach to the appropriate VFP Printer DBF table(s) via ODBC.

Hopefully I have not 'muddied the water', but these are things that I would want to know if it were me considering making changes like this.

Good Luck,
JRB-Bldr

 

[dgrewe]

NeifeCare,
Had to read this a few times, but from what I understand You have a program written in VFP that has a table named 'Printer.dbf'. Each person that runs the app has this printer table on their local machine (it is not in a common place on a network).

Using that assumption, You will have to write a program / script to do the change. VFP is ODBC complient so if you do not have VFP you will have to connect to it and run it throught the ODBC driver. This also means you have to run this code on every computer that has the Application.

You will have to create a line of code for each printer you want to change. Do not worry if the user does not have that printer in their PRINTER.DBF. The code below will not crash if they do not have it in their table.

Assuming you have VFP, Here is sample VFP code to do the job.
when you start VFP, in the Command window type "MODIFY COMMAND FIXPRINTER"

LOCAL lcDir  as Char
LOCAL lcFile as Char
lcDir = "C:\directory\structure\2table\"
lcFile = lcDir + " printer.dbf"
*
USE (lcFile) ALIAS Prntfile exclu
REPLACE ALL ColumnName WITH "NewPrinterName1" WHERE ALLTRIM(lower(FieldName)) = "oldprintername1"
REPLACE ALL ColumnName WITH 'NewPrinterName2' WHERE ALLTRIM(lower(FieldName)) = 'oldprintername2'
...........etc
CLOSE ALL 
QUIT

The UPDATE command can be used instead of REPLACE

LOCAL lcDir  as Char
LOCAL lcFile as Char
lcDir = "C:\directory\structure\totable\"
lcFile = lcDir + " printer.dbf"
*
UPDATE (lcFile) SET ColumnName = "NewPrinterName1" WHERE lower(alltrim(ColumnName1 )) = "oldprintername1"
UPDATE (lcFile) SET ColumnName = 'NewPrinterName2' WHERE lower(alltrim(ColumnName1 )) = 'oldprintername2'
...........etc
CLOSE ALL 
QUIT

Save the File with CTRL+S
If any line is underlined there is a Syntax error on that line.
If not errors then do a Ctrl+W
(Or use the VFP menu to save the file then Exit VFP. )
(P.S. VFP does not care if you use " or '. Just do not mix them on the same line of Code.

This will create a file FIXPRINTER.PRG
Save this file to an external storage device.
Go to each computer and start VFP and in the command widow type "DO E:\FIXPRINTER"
Adjust "E:" to whatever the external device is
Or if all computer are connected to a common domain, you can place the FIXPRINTER.PRG is a common location to all computers.

Then as you go to each computer start VFP
in the command window type
DO '\\domain.server\location\fixprinter.prg'

I wrote this in a hurry, so if there are any errors, I'm sure someone will jump in and straighten me out. I have meetings most of today so I will not be back till Tuesday to check the site.

Hope this helps you.


David W. Grewe Dave

 

[nelifecare]

Thanks for your responses. I was able to connect via ODBC and write MSAccess queries to make changes.