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.
rm wrote:
> 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.
There is a connection available for you
CurrentProject.Connection
Most around here would probably recommend DAO over ADO.
--
Roy-Vidar
On Dec 6, 10:43 am, rm
> 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
On Dec 6, 11:11 am, RoyVidar
> rm wrote:
> > 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.
>
> There is a connection available for you
>
> CurrentProject.Connection
>
> Most around here would probably recommend DAO over ADO.
>
> --
> Roy-Vidar
Sir,
Thank you for the response.
CurrentProject.Connection throws the same error message. - Run-time
error '-2147467256 (80004005)' The database has been placed in a state
by user 'userid' on machine 'machineid' that prevents it from being
opened or locked.
Am I using the connection correctly?
cmd1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" & CurrentProject.Connection
On Dec 6, 10:43 am, rm
> 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.
Got it...
cmd1.ActiveConnection = CurrentProject.Connection
Thank you Roy-Vidar!
rm wrote:
> On Dec 6, 11:11 am, RoyVidar
>> rm wrote:
>>> 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.
>> There is a connection available for you
>>
>> CurrentProject.Connection
>>
>> Most around here would probably recommend DAO over ADO.
>>
>> --
>> Roy-Vidar
>
> Sir,
>
> Thank you for the response.
>
> CurrentProject.Connection throws the same error message. - Run-time
> error '-2147467256 (80004005)' The database has been placed in a state
> by user 'userid' on machine 'machineid' that prevents it from being
> opened or locked.
>
> Am I using the connection correctly?
>
> cmd1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source=" & CurrentProject.Connection
>
Try
Set cmd1.ActiveConnection = CurrentProject.Connection
--
Roy-Vidar