On Dec 6, 10:43 am, rm <gro...@rlmoore.netwrote:
When using ADO in a module inside an Access 2003 database do I need to
open a db connection even though I am working with a table inside the
same database?
I am writing a bit of code to periodically import data from a text
file to a table in a database. The capabilities of the import wizard
are not sufficient to support what we need for manipulating/parsing
the ascii text file.
Here is the code...
Sub Import()
Dim fso As New Scripting.FileSystemObject
Dim tsIn As Scripting.TextStream
Dim InputFile As String
Dim cmd1 As ADODB.Command
'Values from the file
Dim strX As String
Dim strY As String
Dim strZ As String
InputFile = "C:\temp\exp.txt"
' Make sure the file exists
If Not fso.FileExists(InputFile) Then
MsgBox "Cannot find input file."
Exit Sub
Else
'Open the input file.
Set tsIn = fso.OpenTextFile(InputFile)
Set cmd1 = New ADODB.Command
cmd1.CommandType = adCmdText
'################################################# #####
' THE FOLLOWING LINE THROWS AN ERROR MESSAGE
'Run-Time error '-2147467259 (80004005)
'The database has been placed in a state by user 'userid' on
machine 'machine-id'
'that prevents it from being opened or locked.
'################################################# #####
cmd1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source= p:\mydb.mdb"
'Loop through input file. When at Endof Stream becomes
'True, you've reached the end of the file
Do While Not tsIn.AtEndOfStream
buf = tsIn.ReadLine
'if we have a social security number - first 9 #s numeric
' then write record
If IsNumeric(Mid(buf, 1, 9)) Then
strX = Trim(Mid(buf, 1, 9))
strY = Trim(Mid(buf, 11, 25))
strZ = Trim(Mid(buf, 39, 2))
cmd1.CommandText = "INSERT INTO table1(x, y, z)
values('" & strX & "','" & strY & "','" & strZ & "')"
'################################################# #####
' THE FOLLOWING LINE THROWS AN ERROR MESSAGE
' BECAUSE I DO NOT HAVE A CONNECTION TO A DB
'################################################# #####
cmd1.Execute
End If
Loop
'Close the file
tsIn.Close
'Release object references
Set tsIn = Nothing
Set cmd1 = Nothing
Set fso = Nothing
End If
End Sub
