Auto login to SQL

[emikoo]

Hi,

I have found already quite some posts regarding this subject, but can not get it to work in my situation:

I have an Access 2000 db which queries a table on an SQL server. The DSN connection = "MyDSN". The table contains a user_id (MyUser) and passw (MyPassw).

I am looking for the vba code to open the connection to the table (myTbl), in order to avoid having the user entering logid/passw the first time he runs a query.

Any help is appreciated.

Emile

 

[BSman]

Use a pass through query to get the data from the table. The pass through query includes the userid and password as part of its connection string, so your user doesn't have to enter a password or id.

For the quickest retrieval of records, have the user set up the selection criteria, then build a SQL pass through query based on the user defined criteria. Use one name for this query and delete the current query, then redefine the query with your new query information and execute the query (or open a form based on that query).

The trick in building the query (besides making sure to include the connection string information) is that SQL Server SQL is slightly different from Access SQL. Since it's a pass through query, the SQL is actually executed by SQL Server, rather than all data being passed to the Access computer and then selected. One of the more important differences is that SQL Servier SQL uses ' around dates (rather than #).

The speed difference can be amazing. I've got an application with an Access (97) front end with a query builder for I've written to enable the user to pick a table or (SQL Server) view as the data source, then pick any number of columns to define selection criteria and then to pick columns for sorting. When the user clicks one button (Details) a pass through query is created and opened (to display a spreadsheet type of format) with the detailed records returned from the SQL Server database. When the use click the SubTotals button, a grouping query based on the sorting columns is build and executed. Even though some of the source tables contain over 2 million rows, the response can be in seconds as long as an indexed column is use for selecting records.

The best way to approach this is to build a query in SQL Server first so you have an idea of what the correct syntax is for SQL server. Then build the same query in Access as a pass through query and test it. You will then get a better idea of the syntax of the pass through queries that you will be building in Access.

 

[emikoo]

ok thnxs for your feedback. I however do want to go through vba coding. I am not familiar with pass through queries. Additionally I want to run the code based upon Windows 2000 logon information (so some will have read-only, other users admin rights).

looking forward in receiving some feedback regarding the connection string through vba.

Emile

 

[RobCPA]

Here is the code that I use. I pass in the name of the table as displayed in access (tablename) and the name of the table in Oracle (foreignname). You'll want to make sure to delete the link to the table prior to running this code. Also, once any one connection has been established for the particular database, you should no longer see the login/password screen pop up.

dim db as database
dim tdf as tabledef

set db = currentdb

Set tdf = db.CreateTableDef(tablename)
tdf.Connect = "ODBC;DSN=mydsn;DBQ=mydbq;UID=myuserid;PWD=mypassword;"
tdf.SourceTableName = foreignname
db.TableDefs.Append tdf
db.TableDefs.Refresh

 

[BSman]

Here's what I use with a SQL back end. The idea is to create a pass through query in Access that is actually the SQL code as needed by SQL Server so it can run on SQL Server. You should not have any references to anything in Access. This means that you must create a new query each time that includes the actual selection values (rather than references to controls on your forms).

Dim strSQL As String
Dim dbs As DATABASE
Set dbs = CurrentDb
Dim qdf As QueryDef, rst As Recordset
' delete current version of query
dbs.QueryDefs.Delete ("Matching records&quot
Set qdf = dbs.CreateQueryDef("Matching records&quot

qdf.Connect = "ODBC;" & "DSN=(odbcnameofdatabase);" & "UID=(username);" & "PWD=(userpassword);" & "DATABASE=(databasename);" & "Address=(server address)"

strSQL = "SELECT * FROM (SQL table) WHERE " & (your where clause here) & " ORDER BY " & (your sort clause here) & ";"

qdf.SQL = strSQL
' let us see the records
DoCmd.OpenQuery "Matching records"
Set dbs = Nothing

Once you've figured out the right parameters for the connect string for your case it will work very well.

 

[young]

Book "Access Cookbook" ISBN#:0596000847, page 633 tell you everything you need.