Result of INSERT statement not showing up in following SELECT statement

[David]

I am using ADO to run some SQL statements in Access.

First, I run query to create a table using SELECT . . . INTO Table A.

Then, I run a query to SELECT . . . FROM Table A to get some data and
the table is empty.

This appears to be a timing issue because the second query works when
I run it in ACCESS instead of in VB following the first statement.

What is the problem?

How do I know that the first statement has been completed (or
committed) before running the second one?

Thank you.

[Steve Jorgensen]

On Sun, 14 Aug 2005 17:12:30 GMT, David <bo***********@yahoo.com> wrote:

I am using ADO to run some SQL statements in Access.

First, I run query to create a table using SELECT . . . INTO Table A.

Then, I run a query to SELECT . . . FROM Table A to get some data and
the table is empty.

This appears to be a timing issue because the second query works when
I run it in ACCESS instead of in VB following the first statement.

What is the problem?

How do I know that the first statement has been completed (or
committed) before running the second one?

Thank you.

Are you using the same connection instance for each statement?

[David]

On Sun, 14 Aug 2005 10:41:47 -0700, Steve Jorgensen
<no****@nospam.nospam> wrote:

On Sun, 14 Aug 2005 17:12:30 GMT, David <bo***********@yahoo.com> wrote:

I am using ADO to run some SQL statements in Access.

First, I run query to create a table using SELECT . . . INTO Table A.

Then, I run a query to SELECT . . . FROM Table A to get some data and
the table is empty.

This appears to be a timing issue because the second query works when
I run it in ACCESS instead of in VB following the first statement.

What is the problem?

How do I know that the first statement has been completed (or
committed) before running the second one?

Thank you.

Are you using the same connection instance for each statement?

Yes.

[David]

On Sun, 14 Aug 2005 10:41:47 -0700, Steve Jorgensen
<no****@nospam.nospam> wrote:

On Sun, 14 Aug 2005 17:12:30 GMT, David <bo***********@yahoo.com> wrote:

I am using ADO to run some SQL statements in Access.

First, I run query to create a table using SELECT . . . INTO Table A.

Then, I run a query to SELECT . . . FROM Table A to get some data and
the table is empty.

This appears to be a timing issue because the second query works when
I run it in ACCESS instead of in VB following the first statement.

What is the problem?

How do I know that the first statement has been completed (or
committed) before running the second one?

Thank you.

Are you using the same connection instance for each statement?

My first reply was incorrect.

I have a utility module that I pass a SQL statement to for processing.
I made the module so that I wouldn't have to write the same code in
every program. Was this a bad idea? Can I commit a record in Access
similar to Oracle?

I use this to run the statement:

Set recSet = Utilities.Run_SQL(sqlStmt)

The utilities module looks like this:
Option Explicit
Dim db_file As String
Dim sqlStmt As String
Dim dbConn As ADODB.Connection
Dim recSet As ADODB.Recordset
Dim strClient As String

Public Function Run_SQL(SQLs As String) As ADODB.Recordset
Make_Connection

sqlStmt = SQLs

Run_SQL_Stmt

Set Run_SQL = recSet

End Function

Private Sub Make_Connection()
' MsgBox "Entering make_connection"

'Set the database
db_file = "C:\Current Database\new.mdb"

'Open a connection to the database
Set dbConn = New ADODB.Connection

dbConn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"

dbConn.Open (dbConn.ConnectionString)

End Sub

Private Sub Run_SQL_Stmt()
'Run the statement
On Error GoTo ExecuteError

Set recSet = dbConn.Execute(sqlStmt, , adCmdText)

Exit Sub

ExecuteError: frmInvoicing.txtSQLText.Text =
frmInvoicing.txtSQLText.Text & _
vbCrLf & _
"*** Error executing command in Utilities.RunSQL_Stmt ***"
& _
vbCrLf & Err.Description & vbCrLf & sqlStmt
Exit Sub

End Sub

Thanks for your help.

David

[Steve Jorgensen]

On Sun, 14 Aug 2005 17:50:03 GMT, David <bo***********@yahoo.com> wrote:

On Sun, 14 Aug 2005 10:41:47 -0700, Steve Jorgensen
<no****@nospam.nospam> wrote:

On Sun, 14 Aug 2005 17:12:30 GMT, David <bo***********@yahoo.com> wrote:

I am using ADO to run some SQL statements in Access.

First, I run query to create a table using SELECT . . . INTO Table A.

Then, I run a query to SELECT . . . FROM Table A to get some data and
the table is empty.

This appears to be a timing issue because the second query works when
I run it in ACCESS instead of in VB following the first statement.

What is the problem?

How do I know that the first statement has been completed (or
committed) before running the second one?

Thank you.

Are you using the same connection instance for each statement?

My first reply was incorrect.

I have a utility module that I pass a SQL statement to for processing.
I made the module so that I wouldn't have to write the same code in
every program. Was this a bad idea? Can I commit a record in Access
similar to Oracle?

This is specifically a problem with JET, and if I remember correctly, it does
will not occur if you use the same DAO Connection or the same DAO Database
instance for every call. JET has transactions, and it also has a
DBEngine.Idle call (DAO only) that the documentation says will flush all
unwritten data to the datatbase. Neither of these seems to help, though.

Regarding whether your common code was a bad idea, no, removing duplication is
good, you just need a way to open one connection, save it in a variable, and
always use that connection. I like the lazy initialization approach...

private mcnn As ADODB.Connectino

Private Function GetConnection()
Const cstrDbFile = "C:\Current Database\new.mdb"

If mcnn is Nothing Then
Set mcnn = New ADODB.Connection

mcnn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & cstrDbFile & ";" & _
"Persist Security Info=False"

mcnn.Open (dbConn.ConnectionString)
End If

Set GetConnection = mcnn
End Function

Private Sub CloseConnection()

If mcnn is Nothing Then Exit Sub
mcnn.Close
Set mcnn = Nothing

End Sub

Each procedure that needs a connection, now calls this GetConnection to get
it. At the exit point of the program, call CloseConnection to clean up
nicely.

[David]

On Sun, 14 Aug 2005 11:16:40 -0700, Steve Jorgensen
<no****@nospam.nospam> wrote:

On Sun, 14 Aug 2005 17:50:03 GMT, David <bo***********@yahoo.com> wrote:

On Sun, 14 Aug 2005 10:41:47 -0700, Steve Jorgensen
<no****@nospam.nospam> wrote:

On Sun, 14 Aug 2005 17:12:30 GMT, David <bo***********@yahoo.com> wrote:

I am using ADO to run some SQL statements in Access.

First, I run query to create a table using SELECT . . . INTO Table A.

Then, I run a query to SELECT . . . FROM Table A to get some data and
the table is empty.

This appears to be a timing issue because the second query works when
I run it in ACCESS instead of in VB following the first statement.

What is the problem?

How do I know that the first statement has been completed (or
committed) before running the second one?

Thank you.

Are you using the same connection instance for each statement?

My first reply was incorrect.

I have a utility module that I pass a SQL statement to for processing.
I made the module so that I wouldn't have to write the same code in
every program. Was this a bad idea? Can I commit a record in Access
similar to Oracle?

This is specifically a problem with JET, and if I remember correctly, it does
will not occur if you use the same DAO Connection or the same DAO Database
instance for every call. JET has transactions, and it also has a
DBEngine.Idle call (DAO only) that the documentation says will flush all
unwritten data to the datatbase. Neither of these seems to help, though.

Regarding whether your common code was a bad idea, no, removing duplication is
good, you just need a way to open one connection, save it in a variable, and
always use that connection. I like the lazy initialization approach...

private mcnn As ADODB.Connectino

Private Function GetConnection()
Const cstrDbFile = "C:\Current Database\new.mdb"

If mcnn is Nothing Then
Set mcnn = New ADODB.Connection

mcnn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & cstrDbFile & ";" & _
"Persist Security Info=False"

mcnn.Open (dbConn.ConnectionString)
End If

Set GetConnection = mcnn
End Function

Private Sub CloseConnection()

If mcnn is Nothing Then Exit Sub
mcnn.Close
Set mcnn = Nothing

End Sub

Each procedure that needs a connection, now calls this GetConnection to get
it. At the exit point of the program, call CloseConnection to clean up
nicely.

Thanks a lot.

That explains it. I will use your example.

Thanks again.

[David]

On Sun, 14 Aug 2005 11:16:40 -0700, Steve Jorgensen
<no****@nospam.nospam> wrote:

private mcnn As ADODB.Connectino

Private Function GetConnection()
Const cstrDbFile = "C:\Current Database\new.mdb"

If mcnn is Nothing Then
Set mcnn = New ADODB.Connection

mcnn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & cstrDbFile & ";" & _
"Persist Security Info=False"

mcnn.Open (dbConn.ConnectionString)
End If

Set GetConnection = mcnn
End Function

Private Sub CloseConnection()

If mcnn is Nothing Then Exit Sub
mcnn.Close
Set mcnn = Nothing

End Sub

Each procedure that needs a connection, now calls this GetConnection to get
it. At the exit point of the program, call CloseConnection to clean up
nicely.

Perfect.

I put it in my module and it worked great.

Thanks a lot.