Run a query in Access db

  • Thread starter Thread starter Jim
  • Start date Start date
J

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
 
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?
 
¤ 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)
 
Paul said:
¤ 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)
Click to expand...


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
 
¤ 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)
 
Paul said:
¤ 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)
Click to expand...


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
 
Jim said:
Paul said:
On Fri, 15 Jun 2007 11:17:02 -0400, Jim

¤ 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)
Click to expand...


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?
Click to expand...


First, why do you get the exception? Is it because of the missing login to
the sql server? Is there more information than "ODBC - connection to XYZ
failed"? If you execute the query in Access, are you asked for a
password? If you are not, it should also work from your VB application. If
you are, you have two options: either store the UID/PWD in the link to
the SQL server table (AFAIR you are asked when creating the link to the
table), or, that's what Paul probably ment, instead of creating a link to
the table, change the query by replacing the table name
dbo_THG_KEYV_KEY_VALUES by the connection string that Paul mentioned
"[ODBC;....]". Should result in

....FROM tbl_X12 INNER JOIN [ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=yes].[Table1]

Maybe you can add an alias name ("As ...") to reference the table in the
rest of the sql.


Armin
 
Back
Top