Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2005
    Posts
    13

    Question Unanswered: SQL database connection

    I have an Access 97 front-end where I'm converting the back-end from Access 97 to SQL Server 8. A major aprt of this effort is converting the code from DAO to ADO (and I'm using a DSN less connection).

    The connection question I have: I know there's an initial connection to the SQL database required. Once connected to the database, is connecting to SQL tables something needing to be done on a table by table basis ? If so, what ADO properties/methods/events are needed to do so (like a tdf.connect in DAO) ? Just because (1) it seems redundant to issue a new connection string via the rst.open command EACH TIME I want to access a bound form or combo box, and (2) when I access a SQL linked table in development mode, I get prompted for login ID and password the FIRST time, then after that, I can go into any other table without being prompted. WHich makes me think there's some kind of connection I can issue to ALL SQL tables once I'm logged into the database.

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Provided the SQL tables have been linked into SQL server then you can open and view the data without any other connection details. You would use ADO if you want to run a stored procedure on the server.

    I would advise that you don't try to do any data processing using Access 97 - SQL server is the place for that

    Justin

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    currentproject.connection as the connection should do the dirty deed for you. specify it in place of the connection string

  4. #4
    Join Date
    Mar 2005
    Posts
    13
    Quote Originally Posted by justin_tighe
    Provided the SQL tables have been linked into SQL server then you can open and view the data without any other connection details. You would use ADO if you want to run a stored procedure on the server.

    I would advise that you don't try to do any data processing using Access 97 - SQL server is the place for that

    Justin
    By "data processing" you mean developing SQL Server stored procs for the inserting/updating/deleting ? What about existing functionality the Access front-end has to save/delete/insert ? Just want to clarify.

  5. #5
    Join Date
    Mar 2005
    Posts
    13
    Quote Originally Posted by healdem
    currentproject.connection as the connection should do the dirty deed for you. specify it in place of the connection string
    Thanks - need clarification. The way I'm using ADO is as follows: (1) to connect to the database in the login screen, I'm using the code in "Block 1" (2) for subsequent data access, I'm using the code like what you see in "Block 2." The idea being that since a database connection has been established in "Block 1" through the variable gcnx, recordsets can be accessed using that gcnx string.

    Is that the proper way to code for ADO ? My understanding of it was that a db connection was something that was valid while you're logged into an application.

    ----- BLOCK 1 -----------------

    Set gCnx = New ADODB.Connection ' Global Connection object.
    sConnectStr = "Driver=" & LoginDatabaseDriver & ";" & _ 'from ini file
    "uid=" & Trim(Me.txtLogin) & ";" & _ 'from form
    "pwd=" & Trim(Me.txtPassword) & ";" & _ 'from form
    "database=" & LoginDatabaseName & ";" & _ 'from ini file
    "SERVER=" & LoginDatabaseServer & ";" 'from ini file

    With gCnx
    .Errors.Clear
    .ConnectionString = sConnectStr
    .CursorLocation = adUseClient
    .ConnectionTimeout = 15
    .Open
    End With

    ----- BLOCK 2 -----------------

    strSQL = "SELECT * FROM tblEmployees WHERE Upper(LoginName) = '" & UCase(Me.txtLogin) & "'"

    Set rstTable = New ADODB.Recordset
    With rstTable
    .CursorLocation = adUseClient
    .Open strSQL, gCnx, adOpenKeyset, adLockReadOnly, adCmdText
    End With

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Thanks - need clarification.
    Shouldn't.....

    having established your connection in your initial logon screen then you have several options
    one is to use the persist security connection option of the connection properties.

    it is quite possible share that connection within the application. You may need to talk to you DBA's to see if there is a limit of connections that can be handled.Assuming no limits if when you open it in a common module (function) ( or keep your logon form open but hiddent) and retain the number of forms / reports using that connection then dispose of the connection when you have finished with it. Eg end of application or when forms using the conection drope to 0.

  7. #7
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Quote Originally Posted by jmcprogrammer
    By "data processing" you mean developing SQL Server stored procs for the inserting/updating/deleting ? What about existing functionality the Access front-end has to save/delete/insert ? Just want to clarify.
    Once you start using SQL server instead of Access to store you data, you should re-write all your data processing - insert/update/delete in SQL server to get the benefit of SQL server. If you don't then Access will attempt to copy all the data across your network and process the data locally.

    Justin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •