Showing posts with label function. Show all posts
Showing posts with label function. Show all posts

Sunday, March 25, 2012

connecting to SQL express through VB

Hi all,

I have SQL server express edition 2005 installed on my computer and using VB in MS Access to run some queries. The VB function looks something like this..

Dim dataRS As New ADODB.Recordset
Dim cnnSQL As New ADODB.Connection

Set cnnSQL = New ADODB.Connection
cnnSQL.ConnectionString = "Provider=SQLNCLI;Server=.\SQLExpress;Database=TestDB;Trusted_Connection=yes;"

cnnSQL.Properties("Integrated Security").Value = "SSPI"
cnnSQL.Open

dataRS.Open "SELECT dbo_Table_1.* FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic

The code fails on the last line with message
"Invalid object name 'dbo_Table_1'"
(yes, I verified that the table actually does exist in the DB)

I must be doing something wrong with the connection string but all the things that I've tried so far haven't worked.. Can anyone offer any suggestions?

Thanks in advance,

Try providing the schema name for the table in your select statement, as in:

SELECT DBO.dbo_Table1.* FROM DBO.dbo_Table1

You schema may or may not be DBO, I just use that here as an example.

Mike

Tuesday, February 14, 2012

Connect with Access

Can someone point me in the right direction.. I want to connect to and call
either a procedure or function from accessYou can add a linked server to Access from SQL Server. For examples, see
sp_addlinkedserver, and sp_addlinkedsrvlogin in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Brian" <nixtootec@.hotmail.com> wrote in message
news:edIFt3k#DHA.2324@.tk2msftngp13.phx.gbl...
Can someone point me in the right direction.. I want to connect to and call
either a procedure or function from access

Connect with Access

Can someone point me in the right direction.. I want to connect to and call
either a procedure or function from accessYou can add a linked server to Access from SQL Server. For examples, see
sp_addlinkedserver, and sp_addlinkedsrvlogin in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Brian" <nixtootec@.hotmail.com> wrote in message
news:edIFt3k#DHA.2324@.tk2msftngp13.phx.gbl...
Can someone point me in the right direction.. I want to connect to and call
either a procedure or function from access