Run a query in Access db

[Jim]

I'm a vb.net newbie.

I have an query that is entirely within an Access database. It's a
simple update query - "usp_Append_tbl_RefNos". It works in Access,

How can I run that query from a vb .net program? I have an vb app that
does update another Access table using a stored procedure but it updates
fields with info from a file.

I've tried the code below and I get an error on the ExecuteNonQuery() -
"ODBC - connection to 'XYZ' failed". But I never reference "XYZ" server
anywhere in my program.
================================================== ================
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim RowsAffected As Integer
Dim AConnect As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ABC.mdb;"
Dim AccessConnection As New OleDbConnection(AConnect)

Dim AccessCommand As New OleDbCommand
AccessCommand.CommandText = "usp_Append_tbl_RefNos"
AccessCommand.CommandType = CommandType.StoredProcedure
AccessCommand.Connection = AccessConnection
'Dim AccessDataAdapter As New OleDbDataAdapter(AccessCommand)
AccessConnection.Open()

RowsAffected = AccessCommand.ExecuteNonQuery()

AccessConnection.Close()
AccessConnection = Nothing
AccessCommand.Dispose()
AccessCommand = Nothing

End Sub

TIA,

Jim

[Jim]

The problem was that there was a reference to XYZ server in the query in
Access. I can run the query if I make that table local.

Now the problem becomes, how do I setup the connections so that Access
database (below Aconnect) will be able to run the query referencing
another database?

Jim wrote:

I'm a vb.net newbie.

I have an query that is entirely within an Access database. It's a
simple update query - "usp_Append_tbl_RefNos". It works in Access,

How can I run that query from a vb .net program? I have an vb app that
does update another Access table using a stored procedure but it updates
fields with info from a file.

I've tried the code below and I get an error on the ExecuteNonQuery() -
"ODBC - connection to 'XYZ' failed". But I never reference "XYZ" server
anywhere in my program.
================================================== ================
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim RowsAffected As Integer
Dim AConnect As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ABC.mdb;"
Dim AccessConnection As New OleDbConnection(AConnect)

Dim AccessCommand As New OleDbCommand
AccessCommand.CommandText = "usp_Append_tbl_RefNos"
AccessCommand.CommandType = CommandType.StoredProcedure
AccessCommand.Connection = AccessConnection
'Dim AccessDataAdapter As New OleDbDataAdapter(AccessCommand)
AccessConnection.Open()

RowsAffected = AccessCommand.ExecuteNonQuery()

AccessConnection.Close()
AccessConnection = Nothing
AccessCommand.Dispose()
AccessCommand = Nothing

End Sub

TIA,

Jim

[Paul Clement]

On Thu, 14 Jun 2007 09:16:48 -0400, Jim <jo********@RemoveThisStuffNetscape.netwrote:

¤ The problem was that there was a reference to XYZ server in the query in
¤ Access. I can run the query if I make that table local.
¤
¤ Now the problem becomes, how do I setup the connections so that Access
¤ database (below Aconnect) will be able to run the query referencing
¤ another database?

You probably need to include the user ID and password in the connection string (or table link) to
the other database. What kind of database are you working with?

Could you post the query?
Paul
~~~~
Microsoft MVP (Visual Basic)

[Jim]

Paul Clement wrote:

On Thu, 14 Jun 2007 09:16:48 -0400, Jim <jo********@RemoveThisStuffNetscape.netwrote:

¤ The problem was that there was a reference to XYZ server in the query in
¤ Access. I can run the query if I make that table local.
¤
¤ Now the problem becomes, how do I setup the connections so that Access
¤ database (below Aconnect) will be able to run the query referencing
¤ another database?

You probably need to include the user ID and password in the connection string (or table link) to
the other database. What kind of database are you working with?

Could you post the query?
Paul
~~~~
Microsoft MVP (Visual Basic)

The below query, which resides on my Access(2003) db, references a local
table (tbl_X12) and a Linked MS SQL database on another server
(dbo_THG_KEYV_KEY_VALUES).

My question is, how do I "log on" to the SQL server in my VB program?

================================================== ======
usp_Update_tbl_ClaimNo query in Access database:

SELECT tbl_X12.ClaimNo, dbo_THG_KEYV_KEY_VALUES.ELEN_ID,
dbo_THG_KEYV_KEY_VALUES.KEYV_VALUE
FROM tbl_X12 INNER JOIN dbo_THG_KEYV_KEY_VALUES ON tbl_X12.Gwid =
dbo_THG_KEYV_KEY_VALUES.DOCS_ID
WHERE (((tbl_X12.ClaimNo) Is Null) AND
((dbo_THG_KEYV_KEY_VALUES.ELEN_ID)=418)) OR (((tbl_X12.ClaimNo) Is Null)
AND ((dbo_THG_KEYV_KEY_VALUES.ELEN_ID)=468)) OR (((tbl_X12.ClaimNo) Is
Null) AND ((dbo_THG_KEYV_KEY_VALUES.ELEN_ID)=518));
================================================== =======
VB .Net program:

' this works if all tables in the usp_Update_tbl_THG_ClaimNo are local.
' the problem is one table is on an MS SQL server db
' as of today 6/14/7 09:22 I don't know how to reference it in the
below commands

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim RowsAffected As Integer
Dim AConnect As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\837.mdb;"
Dim AccessConnection As New OleDbConnection(AConnect)

Dim AccessCommand As New OleDbCommand
AccessCommand.CommandText = "usp_Update_tbl_ClaimNo"
AccessCommand.CommandType = CommandType.StoredProcedure
AccessCommand.Connection = AccessConnection
'Dim AccessDataAdapter As New OleDbDataAdapter(AccessCommand)
AccessConnection.Open()

RowsAffected = AccessCommand.ExecuteNonQuery()

AccessConnection.Close()
AccessConnection = Nothing
AccessCommand.Dispose()
AccessCommand = Nothing

End Sub
================================================== =======
Thanks in advance.

Jim

[Paul Clement]

On Fri, 15 Jun 2007 11:17:02 -0400, Jim <jo********@RemoveThisStuffNetscape.netwrote:

¤ The below query, which resides on my Access(2003) db, references a local
¤ table (tbl_X12) and a Linked MS SQL database on another server
¤ (dbo_THG_KEYV_KEY_VALUES).
¤
¤ My question is, how do I "log on" to the SQL server in my VB program?
¤
¤ ================================================== ======
¤ usp_Update_tbl_ClaimNo query in Access database:
¤
¤ SELECT tbl_X12.ClaimNo, dbo_THG_KEYV_KEY_VALUES.ELEN_ID,
¤ dbo_THG_KEYV_KEY_VALUES.KEYV_VALUE
¤ FROM tbl_X12 INNER JOIN dbo_THG_KEYV_KEY_VALUES ON tbl_X12.Gwid =
¤ dbo_THG_KEYV_KEY_VALUES.DOCS_ID
¤ WHERE (((tbl_X12.ClaimNo) Is Null) AND
¤ ((dbo_THG_KEYV_KEY_VALUES.ELEN_ID)=418)) OR (((tbl_X12.ClaimNo) Is Null)
¤ AND ((dbo_THG_KEYV_KEY_VALUES.ELEN_ID)=468)) OR (((tbl_X12.ClaimNo) Is
¤ Null) AND ((dbo_THG_KEYV_KEY_VALUES.ELEN_ID)=518));
¤ ================================================== =======
¤ VB .Net program:
¤
¤ ' this works if all tables in the usp_Update_tbl_THG_ClaimNo are local.
¤ ' the problem is one table is on an MS SQL server db
¤ ' as of today 6/14/7 09:22 I don't know how to reference it in the
¤ below commands
¤
¤ Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
¤ System.EventArgs) Handles Button1.Click
¤ Dim RowsAffected As Integer
¤ Dim AConnect As String = _
¤ "Provider=Microsoft.Jet.OLEDB.4.0;" & _
¤ "Data Source=C:\837.mdb;"
¤ Dim AccessConnection As New OleDbConnection(AConnect)
¤
¤ Dim AccessCommand As New OleDbCommand
¤ AccessCommand.CommandText = "usp_Update_tbl_ClaimNo"
¤ AccessCommand.CommandType = CommandType.StoredProcedure
¤ AccessCommand.Connection = AccessConnection
¤ 'Dim AccessDataAdapter As New OleDbDataAdapter(AccessCommand)
¤ AccessConnection.Open()
¤
¤ RowsAffected = AccessCommand.ExecuteNonQuery()
¤
¤ AccessConnection.Close()
¤ AccessConnection = Nothing
¤ AccessCommand.Dispose()
¤ AccessCommand = Nothing
¤
¤ End Sub
¤ ================================================== =======
¤ Thanks in advance.
You would probably need to reference the table directly by specifying the connection string
information:

[ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_ Connection=yes].[Table1]

Of course if you're not using a trusted connection you would replace this information with the user
ID and password.
Paul
~~~~
Microsoft MVP (Visual Basic)

[Jim]

Paul Clement wrote:

On Fri, 15 Jun 2007 11:17:02 -0400, Jim <jo********@RemoveThisStuffNetscape.netwrote:

¤ The below query, which resides on my Access(2003) db, references a local
¤ table (tbl_X12) and a Linked MS SQL database on another server
¤ (dbo_THG_KEYV_KEY_VALUES).
¤
¤ My question is, how do I "log on" to the SQL server in my VB program?
¤
¤ ================================================== ======
¤ usp_Update_tbl_ClaimNo query in Access database:
¤
¤ SELECT tbl_X12.ClaimNo, dbo_THG_KEYV_KEY_VALUES.ELEN_ID,
¤ dbo_THG_KEYV_KEY_VALUES.KEYV_VALUE
¤ FROM tbl_X12 INNER JOIN dbo_THG_KEYV_KEY_VALUES ON tbl_X12.Gwid =
¤ dbo_THG_KEYV_KEY_VALUES.DOCS_ID
¤ WHERE (((tbl_X12.ClaimNo) Is Null) AND
¤ ((dbo_THG_KEYV_KEY_VALUES.ELEN_ID)=418)) OR (((tbl_X12.ClaimNo) Is Null)
¤ AND ((dbo_THG_KEYV_KEY_VALUES.ELEN_ID)=468)) OR (((tbl_X12.ClaimNo) Is
¤ Null) AND ((dbo_THG_KEYV_KEY_VALUES.ELEN_ID)=518));
¤ ================================================== =======
¤ VB .Net program:
¤
¤ ' this works if all tables in the usp_Update_tbl_THG_ClaimNo are local.
¤ ' the problem is one table is on an MS SQL server db
¤ ' as of today 6/14/7 09:22 I don't know how to reference it in the
¤ below commands
¤
¤ Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
¤ System.EventArgs) Handles Button1.Click
¤ Dim RowsAffected As Integer
¤ Dim AConnect As String = _
¤ "Provider=Microsoft.Jet.OLEDB.4.0;" & _
¤ "Data Source=C:\837.mdb;"
¤ Dim AccessConnection As New OleDbConnection(AConnect)
¤
¤ Dim AccessCommand As New OleDbCommand
¤ AccessCommand.CommandText = "usp_Update_tbl_ClaimNo"
¤ AccessCommand.CommandType = CommandType.StoredProcedure
¤ AccessCommand.Connection = AccessConnection
¤ 'Dim AccessDataAdapter As New OleDbDataAdapter(AccessCommand)
¤ AccessConnection.Open()
¤
¤ RowsAffected = AccessCommand.ExecuteNonQuery()
¤
¤ AccessConnection.Close()
¤ AccessConnection = Nothing
¤ AccessCommand.Dispose()
¤ AccessCommand = Nothing
¤
¤ End Sub
¤ ================================================== =======
¤ Thanks in advance.
You would probably need to reference the table directly by specifying the connection string
information:

[ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_ Connection=yes].[Table1]

Of course if you're not using a trusted connection you would replace this information with the user
ID and password.
Paul
~~~~
Microsoft MVP (Visual Basic)

I THINK I understand... but No, I don't...

I have this connection string to my Access database
Dim AConnect As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\837.mdb;"

How do I How do I integrate your suggestion into that?

Jim