RE: Logging in in background
- From: DevalilaJohn <DevalilaJohn@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 15 May 2006 13:31:02 -0700
Andrew, thanks for the reply. The code you sent could work for me, but it
wold take all kinds of modifictions as I'd need to be checking that each SQL
query (view)is connected before using it from Access. Microsoft clearly has
a mechanism that will connect the Access database (.mdb) to the SQL server
and maintain the knowledge of the connection after it has been established
the first time. All of the SQL side queries (views) show as linked tables to
my database and all have connection strings associated with them.
I suspect that there is also an issue on the SQL Server side as I keep
getting the message that there is no trusted connection even though the ODBC
link is configured for SQL Server authentication.
Thanks again for all your help.
Best regards,
John
"Andrew Tapp" wrote:
I once (many years ago) had an issue with an Oracle database where the.
connection would 'time-out' after 10 minutes and would then re-ask for a
userid and password. I got around this by creating a temporary querydef.
The code is listed below (similar to what's been used).
Public Function ConnectToOracle() As Boolean
Dim qdfOracleConnection As QueryDef
Dim recData As Recordset
On Error GoTo err_ConnectToOracle
'assume failure
ConnectToOracle = False
'create a temporary QueryDef object to retrieve data from an Oracle
database
Set qdfOracleConnection = CurrentDb.CreateQueryDef("")
With qdfOracleConnection
'create Oracle connection
.Connect = "ODBC;DSN=" & Dsn & ";SRVR=" & Server & ";UID=" & UserID
& ";PWD=" & PASSWORD
'create select statement and retrieve only 1 record, as a test
.SQL = "SELECT " & TableName & ".* FROM " & TableName & " WHERE
ROWNUM=1;"
'open and close recordset containing 1 record
Set recData = .OpenRecordset()
recData.Close
Set recData = Nothing
End With
ConnectToOracle = True
exit_ConnectToOracle:
Exit Function
err_ConnectToOracle:
DoCmd.Hourglass False
MsgBox Err.Description & " " & Err.Number & Chr(13) & "System cannot be
loaded", vbCritical, “title”
Resume exit_ConnectToOracle
End Function
Hope this helps.
"DevalilaJohn" wrote:
Andrew,
Your suggestion has definitely helped, thank you. I am now scratching my
head on how to keep the connection to the SQL Server persistant. Clearly,
whatever is created in the function in question is lost when the function
goes out of scope, and the function in question is run from the AutoExec.
Once the AutoExec completes (which it does fine now), the next time there is
a need to reference the SQL database, I am back where I started relative to
the prompts. I guess what I am really looking for is how do I mimic Access's
connection method into the SQL database so that the user just starts up the
database and can go from there without the various steps in my original
message?
TIA,
John
"Andrew Tapp" wrote:
When you use Set dbTheDB = CurrentDb, instead you can open the SQL server
database directly, passing the userid and password.
Dim ws As DAO.Workspace
Set ws = DBEngine.Workspaces(0)
Set dbSQLServer = ws.OpenDatabase("", False, False,
"ODBC;Database=SQLServerDB;DSN=SQLServerDSN;UID=UserID;PWD=Password")
You can then use
Set rsRecordSet = dbTheDB.OpenRecordset("cp_local_CPFlags"), where
cp_local_CPFlags is the name of the table in the SQL server database.
Note that if you hardcode the userid and password, this may not be secure if
other people can view your code. You can lock down your modules with
permissions, a password, or by creating an MDE front-end.
Hope this helps.
"DevalilaJohn" wrote:
Here it is, the error/prompt comes at the Openrecordset
Dim dbTheDB As DAO.Database
Dim rsRecordSet As DAO.Recordset
Dim blOK As Boolean
CheckSystemStatus = True
Set dbTheDB = CurrentDb
Set rsRecordSet = dbTheDB.OpenRecordset("cp_local_CPFlags")
With rsRecordSet
.MoveFirst
If .Fields("SystemAvailable") = True Then blOK = True Else: blOK = False
End With
"Andrew Tapp" wrote:
How is it checking the status flag on the server. I assume there may be some
code being run. Could you paste the relavent code and someone should be able
to comment.
"DevalilaJohn" wrote:
I have a little reporting system, Access (2003) front end (.mdb) and SQL
Server (2000) database. The reporting functionality is doing exactly what I
need. When the Access database opens, the first thing it does is check a
status flag on the server. That brings up a sql db login window. Is there
anyway I can pass the ID and password into SQL before checking the status
flag and thus avoid the pop up?
Thanks in advance for you assistance.
Best regards,
John
- References:
- RE: Logging in in background
- From: DevalilaJohn
- RE: Logging in in background
- From: Andrew Tapp
- RE: Logging in in background
- Prev by Date: Re: Send data from Access to another app using VBA & COM
- Next by Date: Re: multiple instances of forms
- Previous by thread: RE: Logging in in background
- Next by thread: Running VBA code on another computer
- Index(es):
Relevant Pages
|
