Database ISAM Example

Keywords: 	 ADO OLE DB Exporting ACCESS Data To ISAMS

I use ACCESS a great deal as primarily middleware, and it works great in that capacity. The one thing missing in ADO that you can do with DAO is to work with ISAM (dBASE, TEXT, LOTUS...), in particular import and export. DAO permits you to get at the DoCmd Object where you can execute functions like TransferDatabase(), TransFerText().

I discovered this functionality with a kind of 'hacked' SQL, but it works very fast. The attached script has extensive comments for working with the Jet OLE DB Provider and ISAM's.

stan littlefield

; ///////////////////////////////////////////////////////
; Winbatch OLE DB Exporting ACCESS Data To ISAMS
;
; Under DAO, the TransFerDatabase(),and TransferText() methods are
; available to create dBASE, HTML, or comma-delimited exports. This
; is possible with ADO if you use a notation involving [ ] delimiters
; and . connectors. Examples are: (assume path=C:\TEMP, file=test
;
; Access Table To dbf File
; cSQL = "SELECT * INTO [dBase III;DATABASE=%path%].[%file%] FROM [RAO]"
;
; Access Query or Pivot table to DBF
; cSQL = "SELECT qryMyPivoter.* INTO [dBase III;Database=%path%].[%file%]
;         FROM qryMyPivoter;"
;
; For export to Text Files, first create a schema.ini file to override
; Registry Settings
; [[MyFile.TXT]
; FORMAT=DELIMITED(|)
; cSQL = "SELECT * INTO [Text;DATABASE=%path%].[%file%.txt] FROM RAO"
;
; [MyExport.DAT]
; FORMAT=FixedLength
; Col1=MyCustomers Text Width 15
; Col2="My Address Field" Text Width 30
; Plus, you can add any other settings like:
; [MyExport.DAT]
; ColNameHeader=True
;
; NOTE: if issuing multiple statments, it may be easier to set up a
;       cmd = ObjectOpen("ADODB.Command")
;       cmd.Commandtext = cSQL
;       cmd.Execute(cSQL,,1)    where 1=adCmdText constant
;
;  Now, want to get data from your ISAM into ACCESS, try something like
;  (note use of single quotes)
;  cSQL   = 'SELECT * INTO [%table%] FROM [%file%] IN "" [dBASE III;DATABASE=%path%]'
;
;
; Stan Littlefield 08/16/2000
; ///////////////////////////////////////////////////////

BoxOpen("Transferring Access Table","Opening Data Source")

;in this example the table= variable is not used
source = "C:\WBDEMO\BLANK.MDB"
path   = "C:\WBDEMO"
file   = "goods"
table  = "AGOODS"
cConn  = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%source%"
DB     = ObjectOpen("ADODB.Connection")
cSQL   = "SELECT * INTO [dBase III;DATABASE=%path%].[%file%] FROM [RAO]"

DB.Open(cConn)

RS     = DB.Execute(cSQL)

DB.close()
ObjectClose(DB)
BoxShut()
exit

Article ID:   W14668

Filename:   Database ISAM Example.txt

File Created: 2000:09:21:15:30:14

Last Updated: 2000:09:21:15:30:14

	ADO DAO
	Collections
	Constants
	Crystal Reports
	DSOFILE
	Errors
	ImageMagick
	IMAPI
	LogParser
	Lotus Notes
	MDAC
	Netscape
	OLE and Outlook
	OLE TUTORIAL
	OLE with Access
	OLE with Acrobat Viewer
	OLE with Excel
	OLE with Google Earth
	OLE with MSIE
	OLE with PowerPoint
	OLE with Word
	OLE with XML
	OpenOffice
	PDF Conversion
	RegExp
	Samples from Users
	SAPI
	Shell
	SQLite
	Task Scheduler
	Textract
	UDFs
	WIA
	WIL Type Viewer
	WindowsInstaller
	WinHttpRequest
	Winsock
	WSH
	Zip

Database Search

TechHome

Database ISAM Example