Run a query in Access db

**Jim** · Jun 14 '07, 01:25 PM

Re: Run a query in Access db

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(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
>
Dim RowsAffected As Integer
Dim AConnect As String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=C:\ABC.m db;"
Dim AccessConnectio n As New OleDbConnection (AConnect)
>
Dim AccessCommand As New OleDbCommand
AccessCommand.C ommandText = "usp_Append_tbl _RefNos"
AccessCommand.C ommandType = CommandType.Sto redProcedure
AccessCommand.C onnection = AccessConnectio n
'Dim AccessDataAdapt er As New OleDbDataAdapte r(AccessCommand )
AccessConnectio n.Open()
>
RowsAffected = AccessCommand.E xecuteNonQuery( )
>
AccessConnectio n.Close()
AccessConnectio n = Nothing
AccessCommand.D ispose()
AccessCommand = Nothing
>
End Sub
>
TIA,
>
Jim

**Paul Clement** · Jun 15 '07, 01:35 PM

Re: Run a query in Access db

On Thu, 14 Jun 2007 09:16:48 -0400, Jim <joSmith2im@Rem oveThisStuffNet scape.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** · Jun 15 '07, 03:25 PM

Re: Run a query in Access db

Paul Clement wrote:

On Thu, 14 Jun 2007 09:16:48 -0400, Jim <joSmith2im@Rem oveThisStuffNet scape.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_K EY_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_KE Y_VALUES.ELEN_I D,
dbo_THG_KEYV_KE Y_VALUES.KEYV_V ALUE
FROM tbl_X12 INNER JOIN dbo_THG_KEYV_KE Y_VALUES ON tbl_X12.Gwid =
dbo_THG_KEYV_KE Y_VALUES.DOCS_I D
WHERE (((tbl_X12.Clai mNo) Is Null) AND
((dbo_THG_KEYV_ KEY_VALUES.ELEN _ID)=418)) OR (((tbl_X12.Clai mNo) Is Null)
AND ((dbo_THG_KEYV_ KEY_VALUES.ELEN _ID)=468)) OR (((tbl_X12.Clai mNo) 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(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim RowsAffected As Integer
Dim AConnect As String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=C:\837.m db;"
Dim AccessConnectio n As New OleDbConnection (AConnect)

Dim AccessCommand As New OleDbCommand
AccessCommand.C ommandText = "usp_Update_tbl _ClaimNo"
AccessCommand.C ommandType = CommandType.Sto redProcedure
AccessCommand.C onnection = AccessConnectio n
'Dim AccessDataAdapt er As New OleDbDataAdapte r(AccessCommand )
AccessConnectio n.Open()

RowsAffected = AccessCommand.E xecuteNonQuery( )

AccessConnectio n.Close()
AccessConnectio n = Nothing
AccessCommand.D ispose()
AccessCommand = Nothing

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

Jim

**Paul Clement** · Jun 21 '07, 05:55 PM

Re: Run a query in Access db

On Fri, 15 Jun 2007 11:17:02 -0400, Jim <joSmith2im@Rem oveThisStuffNet scape.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_K EY_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_KE Y_VALUES.ELEN_I D,
¤ dbo_THG_KEYV_KE Y_VALUES.KEYV_V ALUE
¤ FROM tbl_X12 INNER JOIN dbo_THG_KEYV_KE Y_VALUES ON tbl_X12.Gwid =
¤ dbo_THG_KEYV_KE Y_VALUES.DOCS_I D
¤ WHERE (((tbl_X12.Clai mNo) Is Null) AND
¤ ((dbo_THG_KEYV_ KEY_VALUES.ELEN _ID)=418)) OR (((tbl_X12.Clai mNo) Is Null)
¤ AND ((dbo_THG_KEYV_ KEY_VALUES.ELEN _ID)=468)) OR (((tbl_X12.Clai mNo) 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(B yVal sender As System.Object, ByVal e As
¤ System.EventArg s) Handles Button1.Click
¤ Dim RowsAffected As Integer
¤ Dim AConnect As String = _
¤ "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
¤ "Data Source=C:\837.m db;"
¤ Dim AccessConnectio n As New OleDbConnection (AConnect)
¤
¤ Dim AccessCommand As New OleDbCommand
¤ AccessCommand.C ommandText = "usp_Update_tbl _ClaimNo"
¤ AccessCommand.C ommandType = CommandType.Sto redProcedure
¤ AccessCommand.C onnection = AccessConnectio n
¤ 'Dim AccessDataAdapt er As New OleDbDataAdapte r(AccessCommand )
¤ AccessConnectio n.Open()
¤
¤ RowsAffected = AccessCommand.E xecuteNonQuery( )
¤
¤ AccessConnectio n.Close()
¤ AccessConnectio n = Nothing
¤ AccessCommand.D ispose()
¤ AccessCommand = Nothing
¤
¤ End Sub
¤ =============== =============== =============== ============
¤ Thanks in advance.

You would probably need to reference the table directly by specifying the connection string
information:

[ODBC;Driver={SQ L Server};Server= (local);Databas e=Northwind;Tru sted_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** · Jun 25 '07, 12:35 PM

Re: Run a query in Access db

Paul Clement wrote:

On Fri, 15 Jun 2007 11:17:02 -0400, Jim <joSmith2im@Rem oveThisStuffNet scape.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_K EY_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_KE Y_VALUES.ELEN_I D,
¤ dbo_THG_KEYV_KE Y_VALUES.KEYV_V ALUE
¤ FROM tbl_X12 INNER JOIN dbo_THG_KEYV_KE Y_VALUES ON tbl_X12.Gwid =
¤ dbo_THG_KEYV_KE Y_VALUES.DOCS_I D
¤ WHERE (((tbl_X12.Clai mNo) Is Null) AND
¤ ((dbo_THG_KEYV_ KEY_VALUES.ELEN _ID)=418)) OR (((tbl_X12.Clai mNo) Is Null)
¤ AND ((dbo_THG_KEYV_ KEY_VALUES.ELEN _ID)=468)) OR (((tbl_X12.Clai mNo) 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(B yVal sender As System.Object, ByVal e As
¤ System.EventArg s) Handles Button1.Click
¤ Dim RowsAffected As Integer
¤ Dim AConnect As String = _
¤ "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
¤ "Data Source=C:\837.m db;"
¤ Dim AccessConnectio n As New OleDbConnection (AConnect)
¤
¤ Dim AccessCommand As New OleDbCommand
¤ AccessCommand.C ommandText = "usp_Update_tbl _ClaimNo"
¤ AccessCommand.C ommandType = CommandType.Sto redProcedure
¤ AccessCommand.C onnection = AccessConnectio n
¤ 'Dim AccessDataAdapt er As New OleDbDataAdapte r(AccessCommand )
¤ AccessConnectio n.Open()
¤
¤ RowsAffected = AccessCommand.E xecuteNonQuery( )
¤
¤ AccessConnectio n.Close()
¤ AccessConnectio n = Nothing
¤ AccessCommand.D ispose()
¤ AccessCommand = Nothing
¤
¤ End Sub
¤ =============== =============== =============== ============
¤ Thanks in advance.
>
>
You would probably need to reference the table directly by specifying the connection string
information:
>
[ODBC;Driver={SQ L Server};Server= (local);Databas e=Northwind;Tru sted_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=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=C:\837.m db;"

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

Jim

Run a query in Access db

Run a query in Access db

Comment

Comment

Comment

Comment

Comment