Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 23rd, 2003, 03:37 AM
Registered User
Join Date: Jun 2003
Location: Brighton, Sussex, United Kingdom.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mark.roworth
Default Logging in to SQL Server

Hi group,

I've got an Access DB from which I have link tables to SQL Server. I have created a login form, from which I want the user to provide UID and PW. When the user clicks on Login I need to ensure that the login is valid and set a connection string somewhere to link the CurrentDB through to the SQL Server without the default login popup appearing. Does anyone have any code snippet that does this kind of thing. Would be most grateful. Thanks,


Mark Roworth
Reply With Quote
  #2 (permalink)  
Old June 23rd, 2003, 07:35 PM
Registered User
Join Date: Jun 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

Hi Mark,

This function connects to an sqlserver and authenticates the user using a pre-stored connection string.

eg . ODBC;DSN=xxxxxxxx;DATABASE=xxxxxxxxxxxx;UID=XXX;PW D=xxx

Function cswCheckLink() As Integer
    On Error GoTo Error_cswCheckLink
        Dim wk As dao.Workspace
        Dim dbs As dao.Database
        Dim dbs2 As dao.Database
        Dim rstCust As dao.Recordset
        Dim rsSys As dao.Recordset
        Dim lngError As Long
        Dim SQLServer As Object
        Dim rsODBC As dao.Recordset
        Dim ret As Long

        Set dbs = CodeDb
        Set rsSys = dbs.OpenRecordset("select [connect] from MsysObjects where [name] = 'tblARCustomer'", dbOpenSnapshot)

        If left(rsSys!Connect, 3) = "DSN" Then
                ' create an instance of SQL Server
                Set SQLServer = CreateObject("SQLDMO.SQLServer")
                If Err.Number = 429 Then 'cant create object no SQLServer or MSDE installed
                        DoCmd.Close acForm, "frmSMMessage", acSaveNo
                        GoTo Exit_cswCheckLink
                End If
                ' must be running ODBC
                DoCmd.OpenForm "frmSMMessage"
                Forms!frmsmMessage!lblMessage.Caption = "Checking SQL Server Status"
                Set rsODBC = dbs.OpenRecordset("USysSMODBCDataSource", dbOpenSnapshot)
                If rsODBC.EOF And rsODBC.BOF Then
                        Set rsODBC = Nothing
                        DoCmd.Close acForm, "frmSMMessage", acSaveNo
                        GoTo Exit_cswCheckLink
                       With rsODBC
                                If .fields("ysnTrusted") Then
                                        ' assume trusted connection
                                        ret = SQLServer.PingSQLServerVersion(CStr(Nz(.fields("st rServer"), "")))
                                        ret = SQLServer.PingSQLServerVersion(CStr(.fields("strSe rver")), CStr(.fields("strUserID")), CStr(Nz(.fields("strPassword"), "")))
                                End If
                                If ret > 0 And ret < 256 Then
                                        Set wk = dao.Workspaces(0)
                                        If GetSetting("App", "Main", "ConnectionString", "") <> "" Then
                                                Set dbs2 = wk.OpenDatabase(CStr(rsODBC.fields("strDSN")), dbDriverNoPrompt, False, GetSetting("Image Pro 7", "Global", "ConnectionString", ""))
                                                Set rstCust = dbs2.OpenRecordset("tblARCustomer", dbOpenDynaset, dbSeeChanges)
                                                If lngError = 0 Then
                                                         cswCheckLink = True
                                                 Else ' Tables are Linked, return True
                                                         cswCheckLink = False
                                                 End If
                                                MsgBox "Cannot connect to SQLServer - " & vbNewLine & Err.Description & vbNewLine & "Please confirm that your SQL server is running and online", vbCritical, "App"
                                                cswCheckLink = False
                                        End If
                                End If
                        End With
                        Set rsODBC = Nothing
                End If
                DoCmd.Close acForm, "frmSMMessage", acSaveNo
        End If

        ' Close Recordsets and destroy object variables.
        Set wk = Nothing
        Set dbs = Nothing
        Set SQLServer = Nothing
        Set rstCust = Nothing
        Set rsSys = Nothing
        Set dbs = Nothing
        Exit Function
        lngError = Err.Number
        If Err.Number < -2140000000 Then
                MsgBox "Cannot connect to SQL server " & vbNewLine & "Error is " & Err.Number & vbNewLine & Err.Description
                GoTo Exit_cswCheckLink
        End If
        If Err.Number = 429 Then
                MsgBox "For App to run on a SQL Server database your PC must have the MSDE installed." & vbNewLine & "Please install the MSDE from the CD or from the " & vbNewLine & _
                "Microsoft Office CD", vbCritical, "App"
                GoTo Exit_cswCheckLink
        End If
        Resume Next
End Function

hopes it helps,

Reply With Quote
  #3 (permalink)  
Old March 31st, 2004, 07:03 PM
Registered User
Join Date: Mar 2004
Location: Hobart, Tasmania, Australia.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts

Hi Rich

That's a very thorough function, is there an equivalent using ADO ?

Reply With Quote
  #4 (permalink)  
Old March 31st, 2004, 08:30 PM
sal sal is offline
Friend of Wrox
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts

First, try to implement windows authenticated security. It is more secure and you will not have this issues.

In the meantime, you can wait until the user uses a linked table from the database to be prompted for the username and password, and if youy use ADO you can have them use another connection that youu declare via ADO. Do a search on this forum on connection and you should find the syntax. Just make sure that you set the connection for ado on a module so that you can keeo using it. Do not use currentproject.connection, as this will use your linked tables ant this defeats the purpose of using ADO.

Mark is using SQL-DMO, this can be a royal pain to deploy depending on your network configuration, MDAC, client machine, etc.

Actually, thi is the way you connect using windows authenticated security.

    With mConnection
        .Provider = "MSDataShape"
        .Properties("Data Provider") = "SQLOLEDB.1"
        .Properties("Initial Catalog") = "Northwind"
        .Properties("Persist Security Info") = False
        .Properties("Data Source") = "//servername"
        .Properties("Integrated Security") = "SSPI"
    End With

Reply With Quote
  #5 (permalink)  
Old August 30th, 2004, 01:39 PM
Registered User
Join Date: Aug 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

What do I have to do to make my Windows application/SQL stored procedures use a userID and password from the registry?

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server Reg. SQL Server does not exist error Arsi SQL Server 2000 1 June 11th, 2008 11:20 AM
Logging ASP data to SQL ph0neman Classic ASP Basics 3 March 12th, 2008 09:53 PM
migrating from sql server 2000 to sql server 2005 abinashpatra SQL Server 2005 2 December 1st, 2006 03:45 PM
problem with ms sql-server abd logging hertendreef ASP.NET 2.0 Basics 12 May 1st, 2006 03:15 PM
SSIS - Built-In Logging / Custom Logging ivobecker23 SQL Server 2005 0 April 21st, 2006 05:41 AM

All times are GMT -4. The time now is 07:53 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.