Using VBScript to Merge FoxPro Tables
Travis Taylor
I would like some help to gather some information on how to acomplish
this task before me.
Here is a overview..
I have 2 files that I need to merge, they are in FoxPro 2.5 format. I
don't have Visual FoxPro installed.. these files are created by a
frontend inventory program...I have done this process manually, went
through design phases, and now im ready to try to automate it ala
VBScript.
The Files:
The files will be residing in different directories.
C:\blah\Products.dbf and C:\Blah\1\Products.dbf. C:\Blah\Products.dbf
will be the host for the merge, and all the products from
C:\blah\1\Products.dbf that don't exist in C:\blah\Products.dbf will
need to be moved over to C:\Blah\Products.dbf. Confused? :) Now there
are other parts involved but im sure I have those hammered down...
Where I am confused in trying to find help on the web and most
information I gather is a mix of VB, VBA, and most is concerning ASP.
This is just going to be a script initiated by a user.. no server or
html is involved.. The desktop environment is NT 4.0 SP6a, with MDAC
2.60.xxxx .. This is what I have so far..
Dim oConnStr,Conn,sql,rs
SET Conn = CREATEOBJECT("ADODB.Connection")
oConnStr ="Driver={MicroSoft Visual FoxPro Driver};
SourceDB=C:\Blah\1\;SourceType=DBF;Exclusive=No;BackgroundFetch=No;Collate=Machine;"
Conn.Provider="MSDASQL.1"
sql = "Select * FROM Products WHERE Products.PR_FINELIN=2971"
Conn.open lcConnStr
Set RS=CREATEOBJECT("ADODB.RecordSet")
RS=Conn.execute(SQL)
Conn.Close
'End
This works without error, I ran filemon during this process and
Products.dbf was accessed.. Now how would I go about taking that SQL
statement and inserting it into C:\Blah\Products.dbf ? Ive seen
different ways.. using RecordSet, or just using SQL... The recordSet
option I know I have to define the constants I use which are located
in ADOVBS.INC, but even with those defined, when I try bits of other
peoples code it doesn't seem to work (Even MSDN examples)... Do i
need to create variables for each column to store them then insert
them with SQL? Do I need to concern myself with RecordSet at all? Will
SQL be able to go line by line and do what I need?
Im not really a developer or a programmer, this task was handed to me
and I am enjoying it, but I would like to be heading in the right
direction before to much time is wasted..
Thanks in advance!
/travis taylor
Sukesh Hoogan
Some rough guidelines
1) The SQL query to merge the tables should somewhat as follows as follows
strSQL= "INSERT INTO DestinationTable SELECT * FROM
SourceTable WHERE SomeField NOT IN (SELECT
SomeField FROM DestinationTable)"
3) Conn.Execute (strSQL)
4) There may not be any need to create a recordset.
Sukesh
"Travis Taylor" <travist...@hotmail.com> wrote in message
news:b893dfff.02112...@posting.google.com...
wade.graham
a few problem you are experiencing. I wrote a script a while ago that will
open a user defined database and perform a SQL query. This is not exactly
what you are asking but my script will al least show you some of the ways to
run queries. I will also include some links that I have found. One thing I
would ask is if you find a reindex command or object could you post it.
Links:
http://www.vbsdb.com/sources/pages/documentation/default.asp
http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/32/pid/329
Sorry I can't post more I do not have time to go though my mess I call
bookmarks. The following is the code. It is a little messy considering this
script has been modified some many times that it is a jumble:
'**********************Connect to the
database************************************************************
set cn = CreateObject("ADODB.Connection")
cn.provider = "MSDASQL.1"
'SQL
Statement===================================================================
==========================
strFullPath = "C:\Progra~1\database\activity.dbf"
on error resume next
connstr = "Driver={Microsoft Visual FoxPro Driver}; _
UID=;SourceDB=" & strFullPath & ";SourceType=DBF;"
cn.Open connstr
'sqlStatement = "UPDATE " & strFullPath & " SET " & strAdjustColumn
& "=" & getStringorNum(strAdjustKey) & " WHERE " & strIDColumn & "=" &
getStringorNum(strUnquieKey)
'sqlStatement = "Insert INTO " & strFullPath & " VALUES (40, 'Promo', 100,
100, 1, 1, 0, 'P', 0, 0, 0, 0, '', 0, 0)"
'sqlStatement = "Insert INTO " & strFullPath & " VALUES (40, 1, 1, '',
16777215, 'P', 4000, 0, 0, '', 'vancoke', 1, 0, 0, 0)"
'sqlStatement = "Insert INTO " & strFullPath & " VALUES (0, 40, 0, 690, 0,
0)"
'* Changes a value in a field
'sqlStatement = "UPDATE " & strFullPath & " SET SSN =6666666 WHERE
Last='EMPLOYEE1'"
'Set rs = cn.Execute ("Insert INTO " & strFullPath & " (Desc,
Legend, keycol, keyrow, keycolor, type, Acct_Type, tendcode, subcode,
allow_void, amount_ovr, gas_cash, auto_drop, validate, drop_warn,
drop_block, max_drop, num_Copies, tag, force_prnt, pop_drawer, bitmap,
stretch, quick_ok, visible, inactive ) VALUES ('Oops', 'Oops', 2, 6, 57855,
'OOPS', 'OOPS', 'OOPS', 'OOPS', 1, 999, 0, 1, 0, 0, 0, 0, 1, 0, 1, 0, '', 0,
0, 1, 0 )")
'* Changes several values in a field
'sqlStatement= "UPDATE " & strFullPath & " SET Bitmap ='',
INACTIVE=1, LEGEND='Inactive' WHERE BOARD=40"
'set rs = cn.Execute (sqlStatement)
'set rs = cn.Execute ( "UPDATE " & strFullPath & " SET FEE=.29 WHERE
Bitmap='mord'" )
'* Adds a new line, you seem to have to add all the fields or else
you get errors
'sqlStatement ="INSERT INTO " & strPath & " (User, SSN, First,
Middle, Last, Nickname, USER_LEVEL, Hint_Block) VALUES (8189988, 8891988,
'Hey', 'C', 'Dude', 'Paul', 2, 'T')"
'set rs = cn.Execute (sqlStatement)
'set rs = cn.Execute ("INSERT INTO " & strPath & " (User, SSN, First,
Middle, Last, Nickname, USER_LEVEL, Hint_Block) VALUES (8189988, 8891988,
'Hey', 'C', 'Dude', 'Paul', 2, 'T')")
'* Deletes a row from the DB
'sqlStatement = "DELETE FROM " & strFullPath & " WHERE Bitmap =
'10dpc'"
'Set rs = cn.Execute (sqlStatement)
'* This will get rid of all deleted lines
'set rs = cn.Execute ("PACK " & strFullPath)
'*Closes the database so it can be zipped up
cn.close
set cn = nothing
'*********END**************************
For this you open the 2nd db with inside with a loop then:
'Open DB with the above code
'Open other DB
Do While <> EOF
'Use what ever columns
otherSQL = "UPDATE " & strFullPath & " SET Bitmap =" &
rs.Field("SomeField").Value & " , INACTIVE=" & rs.Field("SomeField").Value
otherDB.Execute(otherSQL)
rs.movenext
Loop
close DB files
I hope this helped. I had to modify it becuase I am using a form object.
This is only part of the code email me if you want the whole
don...@plaidpantry.com
I hope to have helped and not confused
"Travis Taylor" <travist...@hotmail.com> wrote in message
news:b893dfff.02112...@posting.google.com...