We can use several providers to open DBF files usind
ADODB. Basic way is to use ODBC data source (DSN or DSNless), there is several
samples published on web. But how to open DBF files without ODBC? There are two
other OLEDB providers, using which you can work with DBF files. One of them is
Microsoft.Jet.OLEDB.
Key property to work with DBF files is "Extended
Properties" - there is no much info about this parameter in documentation. This
parameter has similar meaning as connect parameter in DAO OpenDatabase method.
First work is to open connection to DBASE IV files:
Function OpenDBFConn(Path)
Dim Conn: Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"Extended Properties=""DBASE IV;"";"
Set OpenDBFConn = Conn
End Function
2. Other ISAM formats, FoxPro
"DBASE IV" is one of ISAM format. You can find installed
ISAM formats in
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ISAM
Formats\ registry key. You can use other formats - "dBase 5.0", "Paradox
3.X", "Outlook 9.0" etc.
But what about FoxPro files? Microsoft.Jet.OLEDB.4.0
does not support FoxPro 2.0 - 3.0 as ISAM parameter - you cannot see this ISAM
engine in Jet\4.0\ISAM Formats lists. You can do some small work around of this
feature - import FoxPro ISAM from Jet\3.5 key. Create small .reg file with the
next information:
REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats\FoxPro 3.0]
"Engine"="Xbase"
"ExportFilter"="Microsoft FoxPro 3.0 (*.dbf)"
"CanLink"=hex:00
"OneTablePerFile"=hex:01
"IsamType"=dword:00000000
"IndexDialog"=hex:00
"CreateDBOnExport"=hex:00
"ResultTextExport"="Export data into a Microsoft FoxPro 3.0 file."
"SupportsLongNames"=hex:00
So, what we can do with the connection? You can use any
ODBC statement (Create table, Insert Into, Delete, Select ...). You can address
the table using several ways:
Select * from Persons
Select * from Persons.DBF
Insert Into Persons#DBF Values (...)
Delete * from [Persons.DBF] Where ...
Create Table [Any Long File Name You Want] As ...
4. Real VBS samples
Open DBF connection, create table, insert records, get recordset.
'Open connection For DBF files In F:\ folder
Dim DBConn
Set DBConn = OpenDBFConn("f:\")
'Create a new DBF file named Persons.DBF
DBConn.Execute "Create Table Persons (Name char(50), City char(50), Phone char(20), Zip decimal(5))"
'Insert some row To the table
DBConn.Execute "Insert into Persons Values('Alex P. Nor', 'Mexico','458962146','14589')"
'Open recordset from Persons table
Dim Persons
Set Persons = DBConn.Execute("Select * from [Persons#DBF]")
'Output the recordset In csv format
Wscript.Echo Persons.GetString(,-1, ", ", vbCrLf)
5. Create DBF table FOXPRO command
See MSDN documentation.
Creates a table having the specified fields.
Specifies the field name, field type, field width, and field precision (number of decimal places), respectively.
A single table can contain up to 255 fields. If one or more fields allow null values, the limit is reduced by one to 254 fields.
FieldType is a single letter indicating the field's data type. Some field data types require that you specify nFieldWidth or nPrecision, or both.
The following table lists the values for FieldType and whether nFieldWidth and nPrecision are required.
FieldType
nFieldWidth
nPrecision
Description
C
n
-
Character field of width n
D
-
-
Date
T
-
-
DateTime
N
n
d
Numeric field of width n with d decimal places
F
n
d
Floating numeric field of width n with d decimal places
I
-
-
Integer
B
-
d
Double
Y
-
-
Currency
L
-
-
Logical
M
-
-
Memo
G
-
-
General
nFieldWidth and nPrecision are ignored for D, T, I, Y, L, M, G, and P types. nPrecision defaults to zero (no decimal places) if nPrecision isn't included for the N or F types. nPrecision defaults to the number of decimal places specified by the SET DECIMAL setting if nPrecision isn't included for the B type.
NULL
Allows null values in the field. If one or more fields can contain null values, the maximum number of fields the table can contain is reduced by one, from 255 to 254.
NOT NULL
Prevents null values in the field.
If you omit NULL and NOT NULL, the current setting of SET NULL determines if null values are allowed in the field. However, if you omit NULL and NOT NULL and include the PRIMARY KEY or UNIQUE clause, the current setting of SET NULL is ignored and the field defaults to NOT NULL.
CHECK lExpression1
Specifies a validation rule for the field. lExpression1 can be a user-defined function. Note that when a blank record is appended, the validation rule is checked. An error is generated if the validation rule doesn't allow for a blank field value in an appended record.
ERROR cMessageText1
Specifies the error message Visual FoxPro displays when the validation rule specified with CHECK generates an error. The message is displayed only when data is changed within a Browse window or Edit window.
DEFAULT eExpression1
Specifies a default value for the field. The data type of eExpression1must be the same as the field's data type.
PRIMARY KEY
Creates a primary index for the field. The primary index tag has the same name as the field.
UNIQUE
Creates a candidate index for the field. The candidate index tag has the same name as the field. For more information about candidate indexes, see "Using Indexes" in Chapter 7, Working with Tables, in the Programmer's Guide.
Note Candidate indexes (created by including the UNIQUE option in CREATE TABLE or ALTER TABLE - SQL) are not the same as indexes created with the UNIQUE option in the INDEX command. An index created with the UNIQUE option in the INDEX command allows duplicate index keys; candidate indexes do not allow duplicate index keys. See INDEX for additional information on its UNIQUE option.
Null values and duplicate records are not permitted in a field used for a primary or candidate index. However, Visual FoxPro will not generate an error if you create a primary or candidate index for a field that supports null values. Visual FoxPro will generate an error if you attempt to enter a null or duplicate value into a field used for a primary or candidate index.
REFERENCES TableName2 [TAG TagName1]
Specifies the parent table to which a persistent relationship is established. If you omit TAG TagName1, the relationship is established using the primary index key of the parent table. If the parent table does not have a primary index, Visual FoxPro generates an error.
Include TAG TagName1 to establish a relation based on an existing index tag for the parent table. Index tag names can contain up to 10 characters.
The parent table cannot be a free table.
NOCPTRANS
Prevents translation to a different code page for character and memo fields. If the table is converted to another code page, the fields for which NOCPTRANS has been specified are not translated. NOCPTRANS can only be specified for character and memo fields.
The following example creates a table named MYTABLE containing two character fields and two memo fields. The second character field CHAR2 and the second memo field MEMO2 include NOCPTRANS to prevent translation.
CREATE TABLE mytable (char1 C(10), char2 C(10) NOCPTRANS,;
memo1 M, memo2 M NOCPTRANS)
PRIMARY KEY eExpression2 TAG TagName2
Specifies a primary index to create. eExpression2 specifies any field or combination of fields in the table. TAG TagName2 specifies the name for the primary index tag that is created. Index tag names can contain up to 10 characters.
Because a table can have only one primary index, you cannot include this clause if you have already created a primary index for a field. Visual FoxPro generates an error if you include more than one PRIMARY KEY clause in CREATE TABLE.
UNIQUE eExpression3 TAG TagName3
Creates a candidate index. eExpression3 specifies any field or combination of fields in the table. However, if you have created a primary index with one of the PRIMARY KEY options, you cannot include the field that was specified for the primary index. TAG TagName3 specifies a tag name for the candidate index tag that is created. Index tag names can contain up to 10 characters.
A table can have multiple candidate indexes.
FOREIGN KEY eExpression4 TAG TagName4 [NODUP]
Creates a foreign (non-primary) index, and establishes a relationship to a parent table. eExpression4 specifies the foreign index key expression and TagName4 specifies the name of the foreign index key tag that is created. Index tag names can contain up to 10 characters. Include NODUP to create a candidate foreign index.
You can create multiple foreign indexes for the table, but the foreign index expressions must specify different fields in the table.
REFERENCES TableName3 [TAG TagName5]
Specifies the parent table to which a persistent relationship is established. Include TAG TagName5 to establish a relation based on an index tag for the parent table. Index tag names can contain up to 10 characters. If you omit TAG TagName5, the relationship is established using the parent table's primary index key by default.
CHECK eExpression2 [ERROR cMessageText2]
Specifies the table validation rule. ERROR cMessageText2 specifies the error message Visual FoxPro displays when the table validation rule is executed. The message is displayed only when data is changed within a Browse window or Edit window.
FROM ARRAY ArrayName
Specifies the name of an existing array whose contents are the name, type, precision, and scale for each field in the table. The contents of the array can be defined with the AFIELDS( ) function.
Remarks
The new table is opened in the lowest available work area, and can be accessed by its alias. The new table is opened exclusively, regardless of the current setting of SET EXCLUSIVE.
If a database is open and you don't include the FREE clause, the new table is added to the database. You cannot create a new table with the same name as a table in the database.
If a database isn't open when you create the new table, including the NAME, CHECK, DEFAULT, FOREIGN KEY, PRIMARY KEY, or REFERENCES clauses generates an error.
Note that the CREATE TABLE syntax uses commas to separate certain CREATE TABLE options. Also, the NULL, NOT NULL, CHECK, DEFAULT, PRIMARY KEY and UNIQUE clause must be placed within the parentheses containing the column definitions.
See also
for 'ASP/VBS, ADO and DBF file database connection.' article
The source code on this page and other samples at http://www.motobit.com/tips/
are a free code, you can use it as you want: copy it, modify it, use it in your products, ...
If you use this code, please:
1. Leave the author note in the source.
or
2. Link this sample from you page.
<A
Href="http://www.motobit.com/tips/detpg_asp-dbf-database/"
Title="Short introduction to work with DBF
files/databases in ASP/VBS/WSH envinronment. Work
with DBF using Microsoft.Jet.OLEDB.4.0 provider."
>ASP/VBS, ADO and DBF file database connection.</A>
IISTracer - IIS ISAPI real-time monitor IISTracer is a real-time monitoring tool for Microsoft IIS, which will show/log you what is happenning on IIS server right now. It let's you reveal problems with long-running scripts (.asp, .cgi, cfm...), hang-up states and low resource situations and lets you stop long-running requests (uploads/downloads).
ActiveX User account Manager - Set of simple objects for creating, deleting, and managing user accounts, groups, servers and domains in the Windows NT environment.
Active log file - Hi-performance text file logging for ASP/VBS/VBA applications. Lets you create daily/weekly/monthly log files with variable number of logged values and extra timing and performance info.
ActiveX windows registry editor - Intuitive, easy to use COM interface to windows registry. Set of classes to read/enumerate/modify windows registry keys and values from ASP, VBS and T-SQL.
ActiveX/ASP Multi Dictionary object - Free-threaded hi-speed dictionary algorithm with unique/nonunique keys (map/multimap). Connect to another dictionary object in the same process. Lock and Unlock methods to synchronize tasks (application scope). Share ASP Application/Session objects.
Export DBF/MDB from ASP - Conversion from recordset to MDB/DBF. Direct binary output of MDB or DBF files from ASP pages with one row of code.
Pure-ASP upload - lets you upload files using Pure ASP VBS code (using multipart/form-data and input type=file).
ByteArray - Works with safearray binary data (VT_UI1 | VT_ARRAY) - save/restore binary data from disk, find, work with code pages, convert to string/hexstring(SQL).
WebChecker - Checks http, https, ftp and gopher internet connections in regular intervals. Lets you monitor web site functionality (uptime). Enables restart or notification on problems.
HTTPLog ISAPI filter - Lets you log incomming/outgoing http header and document data to separate files. Monitor of IIS service input/output.