Use VBA to connect to SQL Server
Nate
SQL Server and am now trying to write my first Access 2007 app, so
please forgive any newbie mistakes.
I found out first hand that the bug described at http://support.microsoft.com/kb/168336
is a major problem for me. I'm trying to link to SQL data that I
didn't write and I can't modify the table structure. I want to run a
bunch of queries against the SQL data, but consistently get records
that don't match the filter criteria from the linked tables. I can
write and save views in SQL. I'd hoped to be able to modify this code
rstTest.Open strPassSQL, CurrentProject.Connection, adOpenStatic,
adLockOptimistic
so that I can change strPassSQL to a variety of SQL strings that
reference SQL views and then open those views directly as opposed to
linking each table to Access 2007 and using
CurrentProject.Connection. Even better would be to be able to use a
direct connection to a SQL view and then apply criteria that actually
work so that I don't have to have so many unique views in SQL.
My questions:
1. How do I set up a connection directly to SQL to replace
CurrentProject.Connection?
2. Will doing that allow me to use criteria in strPassSQL and have
Access return the correct data?
I hope this long post makes sense. Many thanks in advance. This one
has really frustrated me.
Nate
Philipp Stiefel
> I found out first hand that the bug described at http://support.microsoft.com/kb/168336
> is a major problem for me.
What bug? The KB-Article you linked to is a How-To-Article and
does not deal with any particular bug.
> I'm trying to link to SQL data that I
> didn't write and I can't modify the table structure. I want to run a
> bunch of queries against the SQL data, but consistently get records
> that don't match the filter criteria from the linked tables. I can
> write and save views in SQL. I'd hoped to be able to modify this code
>
> rstTest.Open strPassSQL, CurrentProject.Connection, adOpenStatic,
> adLockOptimistic
>
> so that I can change strPassSQL to a variety of SQL strings that
> reference SQL views and then open those views directly as opposed to
> linking each table to Access 2007 and using
> CurrentProject.Connection. Even better would be to be able to use a
> direct connection to a SQL view and then apply criteria that actually
> work so that I don't have to have so many unique views in SQL.
>
> My questions:
>
> 1. How do I set up a connection directly to SQL to replace
> CurrentProject.Connection?
Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strConn as String
strConn = "Provider=SQLOLEDB;Data Source=YourSever;" & _
"Initial Catalog=YourDB;User Id=YourUser;Password=YourPWD;"
Set cn = New ADODB.Connection()
cn.open strConn
Set rs = New ADODB.Recordset
rs.Open yourSQL, cn, adOpenStatic, adLockOptimistic
...
If you have any specific requirements regarding your connection
string please visit http://connectionstrings.com/.
> 2. Will doing that allow me to use criteria in strPassSQL and have
> Access return the correct data?
Yes, it should! - If it doesn't, please post the SQL you are
actually using. Maybe there is something wrong there.
Cheers
Philipp
Nate
>
> > 2. Will doing that allow me to use criteria in strPassSQL and have
> > Access return the correct data?
>
> Yes, it should! - If it doesn't, please post the SQL you are
> actually using. Maybe there is something wrong there.
>
> Cheers
> Philipp
I apologize for sending the wrong bug link. It was a very, very
frustrating evening. The correct link is http://support.microsoft.com/kb/924743.
I'm trying to link to SQL views. The views have a unique field, but,
I can't seem to get Access to properly apply criteria to a view in SQL
as a linked table, so I'm very encouraged by your reply to #2. (Maybe
Access can properly apply criteria if there's a primary key in a
table, but not if it's only a unique field in a view???) If I can
link directly to SQL, I'll write the VBA code and apply criteria that
way.
Thanks for the link on connection strings. I've always been able to
get away with opening a recordset pointed to CurrentProject.Connection
in the past.
Here's my string. What am I missing? The error message is "Invalid
connection string attribute."
Dim rstTest as New ADODB.Recordset
Dim Connection As New ADODB.Connection
Connection.Open
"Provider=SQLOLEDB;Server='SERVER1\Location';Database='Database
name';UserId='username';Password='password'"
rstTest.Open strPassSQL, Connection, adOpenStatic, adLockOptimistic
I'm confused because Server, Data Source, Initial Catalog, Password,
User ID, etc don't seem to be properties of an ADODB.Connection
object. (At least they aren't in the list Access provides after the
period.)
Philipp, thanks so much for the quick, helpful response.
Nate
Philipp Stiefel
> The correct link is http://support.microsoft.com/kb/924743.
> I'm trying to link to SQL views. The views have a unique field, but,
> I can't seem to get Access to properly apply criteria to a view in SQL
> as a linked table, so I'm very encouraged by your reply to #2. (Maybe
> Access can properly apply criteria if there's a primary key in a
> table, but not if it's only a unique field in a view???)
Usually Access has no problems with linked views from an ODBC
datasource if you pick a unique column when promted to do so.
However there may be problems if the unique column in the
view has a datatype (e.g. the BigInt-Datatype) that Access
can not handle properly.
> If I can
> link directly to SQL, I'll write the VBA code and apply criteria that
> way.
You can do that, sure. - But with linked tables (and views)
you can work much easier and effective in Access.
> Here's my string. What am I missing? The error message is "Invalid
> connection string attribute."
>
> Dim rstTest as New ADODB.Recordset
> Dim Connection As New ADODB.Connection
> Connection.Open
> "Provider=SQLOLEDB;Server='SERVER1\Location';Database='Database
> name';UserId='username';Password='password'"
You don't need to enclose the literals in delimiters. - In fact,
I've never seen that before.
Why didn't you build your connection string based on the sample
I wrote? - That was "tested to work".
I suspect that the "Sever" and "Database" attributes are not
recognized by the OLEDB-Provider.
> rstTest.Open strPassSQL, Connection, adOpenStatic, adLockOptimistic
>
> I'm confused because Server, Data Source, Initial Catalog, Password,
> User ID, etc don't seem to be properties of an ADODB.Connection
> object. (At least they aren't in the list Access provides after the
> period.)
The items in the connection string aren't properties of the
ADODB.Connection. Most of the connection string is meaningless
for the ADODB.Connection itself but is only passed through
to the OLE-DB-Provider.
Regards
Philipp
Mary Chipman [MSFT]
This paper describes how to create links to views and set the unique
index in Access: Optimizing Microsoft Office Access Applications
Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx
For troubleshooting, one indispensable tool is SQL Server Profiler.
Profiler allows you to see what's going on under the hood with your
Access-SQL Server application by creating a trace that records all of
the procedure calls between the client application and the server.
Here's a link to get you started
http://msdn.microsoft.com/en-us/library/ms187929.aspx. There are also
many excellent third-party resources available, such as
http://www.sql-server-performance.com/tips/sql_server_profiler_tips_p1.aspx.
HTH,
Mary
On Tue, 15 Sep 2009 07:17:25 -0700 (PDT), Nate <nat...@yahoo.com>
wrote:
Nate
Thanks again, Philipp.
The simple reason I didn't use the sample you wrote was that I wasn't
sure what Data Source and Initial Catalog meant and my Database name
had spaces in it. I followed your advice and the connection worked
great. Thanks for that.
After I set up the connection as you recommended, I played with the
code a bit and ended up with the following code. It appears to work
without even setting up the connection, just passing a string to the
appropriate parameter when I open the recordset.
Dim strConn As String
strConn = "Provider=SQLOLEDB;Data Source=DataSourceName" & _
"Initial Catalog=User Name With Spaces;User
Id=User;Password=Password"
rstTest.Open strPassSQL, strConn, adOpenStatic, adLockOptimistic
I'm able to call this subroutine with multiple strPassSQL strings and
it works great.
I appreciate your help.
Nate
Nate
<mc...@online.microsoft.com> wrote:
> To add to what Philipp advised you:
>
> This paper describes how to create links to views and set the unique
> index in Access: Optimizing Microsoft Office Access Applications
>
> For troubleshooting, one indispensable tool is SQL Server Profiler.
> Profiler allows you to see what's going on under the hood with your
> Access-SQL Server application by creating a trace that records all of
> the procedure calls between the client application and the server.
> many excellent third-party resources available, such ashttp://www.sql-server-performance.com/tips/sql_server_profiler_tips_p....
> >I'm trying to link to SQL views. The views have a unique field, but,
> >I can't seem to get Access to properly apply criteria to a view in SQL
> >as a linked table, so I'm very encouraged by your reply to #2. (Maybe
> >Access can properly apply criteria if there's a primary key in a
> >table, but not if it's only a unique field in a view???) If I can
> >link directly to SQL, I'll write the VBA code and apply criteria that
> >way.
>
> >Thanks for the link on connection strings. I've always been able to
> >get away with opening a recordset pointed to CurrentProject.Connection
> >in the past.
>
> >Here's my string. What am I missing? The error message is "Invalid
> >connection string attribute."
>
> >Dim rstTest as New ADODB.Recordset
> >Dim Connection As New ADODB.Connection
> >Connection.Open
> >"Provider=SQLOLEDB;Server='SERVER1\Location';Database='Database
> >name';UserId='username';Password='password'"
>
> >rstTest.Open strPassSQL, Connection, adOpenStatic, adLockOptimistic
>
> >I'm confused because Server, Data Source, Initial Catalog, Password,
> >User ID, etc don't seem to be properties of an ADODB.Connection
> >object. (At least they aren't in the list Access provides after the
> >period.)
>
> >Philipp, thanks so much for the quick, helpful response.
>
> >Nate
Mary,
That was very helpful. I have a much more complicated Access front-
end linked to a SQL back end that I've hesitated upgrading to Access
2007. This article give me more courage to try.
Nate