471,089 Members | 1,232 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,089 software developers and data experts.

Need to Open a Connection from "inside" a Database?

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.
Dec 6 '07 #1
5 2404
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
Dec 6 '07 #2
rm
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
Dec 6 '07 #3
rm
On Dec 6, 11:11 am, RoyVidar <roy_vidarNOS...@yahoo.nowrote:
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

Dec 6 '07 #4
rm
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.
Got it...

cmd1.ActiveConnection = CurrentProject.Connection

Thank you Roy-Vidar!
Dec 6 '07 #5
rm wrote:
On Dec 6, 11:11 am, RoyVidar <roy_vidarNOS...@yahoo.nowrote:
>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
Dec 6 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by David Furey | last post: by
11 posts views Thread by Pete Wilson | last post: by
10 posts views Thread by qazmlp | last post: by
12 posts views Thread by G Patel | last post: by
2 posts views Thread by Mehdi | last post: by
3 posts views Thread by Peter Proost | last post: by
reply views Thread by Angrez Singh | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.