Microsoft SQL Server forum - MS Access Frontend for remote sql server

Author

MS Access Frontend for remote sql server

aspsql

2006-01-27, 1:23 pm

I have a website which runs off a Access database which I am currently
converting to sql server database. I would like to still use my access
front end for reporting and queries..
I created an odbc connection on my computer to the remote sql server
and created a link in access to my sql server tables. All my report and
Queries run fine I just have one problem it keeps on prompting me for
ther password of the remote sqlserver database. I check the odbc
connection and I see the password is not saved in there even though I
put it in. Is there a way I can have the password saved. I do not want
keep on having to enter the password.

Jens

2006-01-27, 8:24 pm

How did you link that table to Access ? I you used the ODBC
administrator for Creating a DSN and using this Access, make sure that
the password is setup. If so that should be no problem connecting to
the database, unless you use the right password. So try retyping in the
ODBC Administrator the *RIGHT* password.

HTH, jens Suessmeyer,

Bill Ramsey

2006-01-28, 11:23 am

On 27 Jan 2006 14:08:49 -0800, "Jens" <Jens@sqlserver2005.de> wrote:

>How did you link that table to Access ? I you used the ODBC
>administrator for Creating a DSN and using this Access, make sure that
>the password is setup. If so that should be no problem connecting to
>the database, unless you use the right password. So try retyping in the
>ODBC Administrator the *RIGHT* password.
>
>HTH, jens Suessmeyer,

There are ways to re-connect to your tables in VBA code.

One option is to re-link in code to tables one by one, and call a
function with a list of table names. (Note - requires hard coding the
user name and password - see below on security concerns.)

Function LinkTable(tblName As String) As Boolean
'links or re-links a single table
'returns true or false based on err value

Dim db As DAO.Database
Dim tdf As DAO.TableDef

On Error Resume Next
Set db = CurrentDb()
'if the link exists, delete it
Set tdf = db.TableDefs(tblName)
If Err.number = 0 Then
'table link exists
db.TableDefs.Delete tblName
db.TableDefs.Refresh
Else
Err.Clear
End If

'create the link
Set tdf = db. CreateTableDef(tblNa
me)
tdf.Connect = "ODBC;Database=" & strSQLDB & ";DSN=" & strDSN &
" ;uid=username;pwd=pa
ssword;"
tdf.SourceTableName = tblName
db.TableDefs.Append tdf
If Err.number = 0 Then
LinkTable = True
Debug.Print "Table " & tblName & " linked"
Else
LinkTable = False
End If

db.Close
Set db = Nothing
End Function

Note: You are hard coding the user name and password into the connect
string. This option actually can be fairly secure **provided** that
you have the modules locked down via permissions.

Also note - MS-Access is known for it's lack of real security. You can
enhance it by proper network security. Another option is creating a
**MDE file** -- where the modules will be in a binary format and thus
more secure.

Another solution is to connect as a specific user when the database
starts up (Note - this is not secure because you have to code the
password into a connect string . . . use this only in situations where
you can control access to the database through network security). You
can create a specific MS-Access "user" for the connection string with
just enough permissions to accomplish what you need.

Example:
In a form which opens upon startup include a call to a function like
this:
Call PreConnectWithODBCPa
rameters(strSQLDB, strDSN, "MSAccess_Name",
"password")

In a module:
Sub PreConnectWithODBCPa
rameters(sSQLDatabas
e As String, sDSN As
String, sUserName As String, sPassword As String)
Dim dbLocal As Database
Dim strConnectInfo As String
Set dbLocal = DBEngine.Workspaces(0).Databases(0)
strConnectInfo = "ODBC;database=" & sSQLDatabase & ";dsn=" & sDSN
& ";" & _
"uid=" & sUserName & ";" & _
"pwd=" & sPassword & ";"
Set dbLocal = OpenDatabase("", False, False, strConnectInfo)
Set dbLocal = Nothing
End Sub

Bill

Danieltbt05@gmail.com

2006-01-31, 11:24 am

Hi, i was wondering how do you create a connection to sql server. I'm a
novice in sql server. Pls advise. Thanks

Daniel

aspsql wrote:
> I have a website which runs off a Access database which I am currently
> converting to sql server database. I would like to still use my access
> front end for reporting and queries..
> I created an odbc connection on my computer to the remote sql server
> and created a link in access to my sql server tables. All my report and
> Queries run fine I just have one problem it keeps on prompting me for
> ther password of the remote sqlserver database. I check the odbc
> connection and I see the password is not saved in there even though I
> put it in. Is there a way I can have the password saved. I do not want
> keep on having to enter the password.

Sponsored Links