Databases Reference
In-Depth Information
17.6.2.2 Connecting to a text file
The TextExample procedure, shown in Example 17-8, illustrates how to create a text file
and add text to it using the ODBC provider for OLE DB. (Before running this procedure,
you will probably want to change the
DefaultDir
value.)
Example 17-8. The TestExample procedure
Sub TextExample( )
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sCS As String
Dim sSQL As String
' Declare new connection
Set cn = New ADODB.Connection
' Form connection string
sCS = "DefaultDir=d:\bkado;"
sCS = sCS & "Driver={Microsoft Text Driver (*.txt; *.csv)};"
sCS = sCS & "DriverId=27;"
cn.ConnectionString = sCS
cn.Open
' Get full connection string after opening
Debug.Print "Full connection string: " & cn.ConnectionString
' Create a new text file and add a line
On Error Resume Next
cn.Execute "CREATE TABLE [newfile.txt] (FirstName TEXT, LastName
TEXT);"
If Err.Number <> 0 And Err.Number <> vbObjectError + 3604 Then
MsgBox "Error: " & Err.Number & ": " & Err.Description
Err.Clear
End If
sSQL = "INSERT INTO [newfile.txt] (FirstName, LastName) Values
('steve', 'roman');"
cn.Execute sSQL
' Open a recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM NewFile.txt", cn, adOpenDynamic,
adLockOptimistic
' Check support options while we are here
Debug.Print
Debug.Print "Client-Side Dynamic Recordset:"
Debug.Print "adAddNew: " & rs.Supports(adAddNew)
Debug.Print "adBookmark: " & rs.Supports(adBookmark)
Debug.Print "adDelete: " & rs.Supports(adDelete)
Debug.Print "adFind: " & rs.Supports(adFind)
