Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: SQL Server 2005 with Acess Connection String

    Hi,

    I used before MSDE SQL Server 2000 that is free of version of Microsoft for a single pc user.

    I had kept my back end on this server and FE as Access db that was working smoothly thru ODBC linking tbl method. But all the tbls were become heavy gradually and it seems it decreased the speed that I was not expected.

    I am planing to shift from the above server to SQL Server 2005 which I already installed on my laptop and connected to my Access db FE to SQL Server 2005 using same ODBC method and linked all tables. This also works smoothly as SQL Server 2005 has better speed I feel than MSDE Server 2000

    But I do not want to use ODBC linked table in db anymore. I need them to connect temporarily when db opens and de0link at the close so that there will be no tbls displayes in db.

    So basically what I am looking for is connection string module code and I like to keep behind the main form so once it opens, the connection to Server would establish.

    I have seen a small connect string with .net project connected to SQL Server 2005 which is below.

    Module ModConnection
    Public Conn As New ADODB.Connection
    Public Sub Connect()
    Conn = New ADODB.Connection
    Conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
    Conn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;DSN=shhg"
    Conn.Open(Conn.ConnectionString)
    End Sub
    End Module

    May be it would be similar to this.

    Can somebody help me?

    Thanks for your time.

    Ashfaque
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Have a look at the attached database. The entry points to connect tables on the fly are in the F_MyApplication_Main form.
    Attached Files Attached Files
    Have a nice day!

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Sinndho,

    I have gone thru the sample db you attached.

    Looks hard codings. Does it mean if I replaced all tbl names in Tbl_MyApplication_ServerTables with mine and run the form it would run?

    Is there anywhere DSN name mentioned? How about the authentication? I mean my server have password.

    Please extend your help.

    Ashfaque
    With kind regards,
    Ashfaque

  4. #4
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Yes, it works good.

    I did below things

    1. I placed my tbl names with prefix as dbo_ in the tbl "Tbl_MyApplication_ServerTables" of your sample db.
    2. In vba code of your form "F_Connections", I made below code as comment
    ' Me.AllowEdits = DevelopmentPlatform
    because this will enable me to change server name and database name as well which I changed to my server & db name. Saved it. Then again removed appostrophy of code line again.
    3. Run the form "F_MyApplication_Main"

    And it connected my tables once open the sample db and removes when it close.

    One question, how about the username and password in the connection code? Because my connection is trusted one. It has password let us say 1234 and user is sa.

    Thanks for help.
    With kind regards,
    Ashfaque

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As you can see it's not that hard !

    In normal use, the connection string to the server is stored in a property of the Database objects ("ODBC_Server_Connect"). This connection string is provided by the "Cls_SQLConnector" class in its "Connection" property. You can manipulate this property as well as it's almost twin ("SQL_Server_Connect", not in use here) with the "F_Connections" form. As an alternative to work with user defined properties of the Database object, I included the "Mod_Properties" module.

    This class can also derive the string from some keys in the registry (I use it for debugging purposes mainly) and that's why I included the "Cls_Registry" class in my example. If you do not intend to use the registry at all, you can comment or remove the code inside the "Registry" function in the "Mod_CodeHelper" module, but keeping it as it is does not harm. I can provide a full explanation on how to work with the registry keys if you"re interested.

    As you correctly deduced, the names of the tables to attach are stored in the "Tbl_MyApplication_ServerTables" table:

    "Attach" (True/False) indicates whether the table in the "TableName" column must be attached or not.

    "UniqueConstraint" (True/False) indicates whether a unique constraint must be created on the "UniqueKeyName" column of the attached table. If you do not create this unique constraint, you would not be able to modify the contents of the attached table (search for "Identity column" for more details about that).

    "ServerOffset" and "ProviderOffset" are not in use in the example I provided. Those values are related to the way you may compose the connection string from key values in the registry.

    When I use this technique to attach tables from a server, the "F_MyApplication_Main" form is open at the application startup and remains open but hidden for the whole time the application runs. When you quit the application, the form is closed and the attachments are removed.

    If you want to work with SQL Server integrated security, you must change the connection string and replace the "Trusted_Connection=Yes" part with the proper security parameters for the kind of connection you use. See:
    Connecting to a SQL Server Data Source
    or:
    Connection Strings

    I hope this helps.
    Have a nice day!

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I might recommend still using linked tables. If you absolutely cannot use linked tables, you lose the nice ability to quickly create queries and bound forms. Otherwise, all your forms will need to be designed unbound with functions (such as you're designing) to connect to SQL Server and retrieve the data and then send it to the forms (and vice-versa when writing).

    I personally didn't find any advantage to writing views/stored procedures and then vba code to connect to SQL Server and run those versus if I just linked the tables. I prefer to use linked tables because it opens a lot more you can do within the MSAccess interface (ie. you don't have to code to populate any listboxes/comboboxes/etc...etc...) And there isn't any real noticable speed difference unless you're getting into millions of records. - ie. you can achieve relatively the same speed by using linked tables and UNbound form designs.

    What I do is design all my tables in MSAccess (making sure to setup my primary keys, relationships, and correct joins.) I then use the Upsizing Wizard to upsize the tables/data to SQL Server (works great!!) Since I named all my tables with the prefix dbo_tblCustomers, dbo_tblBusinesses, etc..in MSAccess, then after upsizing, I open Enterprise Manager and just rename the tables without the dbo_ prefix (SQL Server nicely retains all the relationships without any problems). THEN when I link the tables into the mdb, I don't need to change the name of the tables or any of my coding! All the tables will link in with the prefix dbo_ and the app is ready to go.
    Last edited by pkstormy; 03-17-10 at 00:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Pkstormy,

    You can still use bound forms with this technique. You simply need to link the table(s) before opening these forms. As for the queries, you're right: they become unusable if they use attached tables ("local" queries still work, of course) and you have to replace them with views. Views that becomes attached tables in the application.
    Have a nice day!

  8. #8
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Run-time error 3270 - Property not found

    PKstormy is right. If I use unbound form and linked tbl that would be fair enough.

    PK, I already have all my forms unbound and subforms are bounded thats stranage because I did not know the inner code loop to save SUBFORM'S unbound text boxes data. Hence I have kept my main forms unbounded and subform with query bounded.

    I would appreciate it very much If you can load any sample db with few unbound fields on main form and few unbound fields in subform keeping save command botton on main form. I need to know this trick.

    Secondly, I wish in my carrier that no linked tbl to be displayed and as you said;

    Otherwise, all your forms will need to be designed unbound with functions (such as you're designing) to connect to SQL Server and retrieve the data and then send it to the forms (and vice-versa when writing).
    I would love this trick but no idea how to do that. Appreciate sample code with your valuable guidelines.

    Sindho,

    I tried with your sample db to connect SQL Server and works fine. But when I imported your form and modules including tbl (changed names accordingly) and run the form, it displays subject error and stops at below code line.

    Run-time error 3270
    Property not found

    Public Property Get Connection() As String

    Connection = m_strProvider & m_strServer
    If Connection = "" Then Connection = CurrentDb.Properties("ODBC_Server_Connect")

    End Property

    Can you please let me know what is remain to set up?

    Thanks in advance
    With kind regards,
    Ashfaque

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You have to create the user-defined property "ODBC_Server_Connect" and add it to your database first. This property does not exist by default. You can use the "CreateDBProperty" function in the "Mod_Properties" module to do so. In the immediate window of the VBA editor, just type:
    Code:
    CreateDBProperty "ODDC_Server_Connect", "<Connection string>"
    with "<Connection string>" being the connection string you intend to use to connect your application to the server.

    If you create both "ODDC_Server_Connect" and "Jet_Server_Connect" properties, you will then be able to use and modify them with the "F_Connections" form.
    Have a nice day!

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you are going to use Access with SQL Server then why not use an ADP (Access Data Project) instead of an MDB? You will be able to use Forms, Reports, and Modules and the Tables and Views will come from SQL Server.

    The advantage to doing this, in my opinion, is the security. You can set up security in SQL Server that dictates what users in your ADP are allowed to do with the data.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by DCKunkle View Post
    If you are going to use Access with SQL Server then why not use an ADP (Access Data Project) instead of an MDB? You will be able to use Forms, Reports, and Modules and the Tables and Views will come from SQL Server.

    The advantage to doing this, in my opinion, is the security. You can set up security in SQL Server that dictates what users in your ADP are allowed to do with the data.
    Ashfaque -

    Ouch. I would avoid ADP as if it were the plague. Only because you lose soo much capability with ADP (ie. queries) and everything must be hard-coded and there's no advantage.

    I typically just link the tables and then design queries/code around the linked tables. If I want to make the forms unbound (to speed up slow connections to the network and prevent lockups of the mdb), it's a simple matter of opening recordsets in code and writing functions to write/update data to/from unbound forms. The paybacks are extremely beneficial without loss in performance (providing connection to the SQL Server and such is efficient)

    The 'ONLY' advantage I see to ADP is that you can manipulate the table structure in the ADP development itself. (which you can also easily do via the SQL Server management)

    Otherwise, link the tables into your mdb and then code accordingly as if they were actually MSAccess tables. I stopped developing ADP's since it's 2-3 times longer for development and there really isn't any speed difference when you're coding unbound forms. Security still applies as normal.

    ADP in my opinion is a dead technology. Microsoft doesn't support ADP very well in newer versions and I found problems with ADP depending on the version of SQL Server.

    I also control security via SQL Server as well as the code bank example on user permissions in the mdb example (see page 6 or 7 in the code bank).

    I personally use a combination of bound and unbound forms (bound forms for simple popup forms which are only open and then close and the user doesn't leave open for a period of time.) Otherwise unbound forms for those forms which the user might keep open, leaving for lunch while they are open (and hence don't take a toll on the constant connection to SQL Server.) Forms that you anticipate the user would leave open (again, while leaving for lunch), should be unbound, especially for slow connections (guarantee a slow user connection will eventually lock the mdb on a bound form.)

    For example, a 'bound' Main Menu form to a table (such as the horrible 'switchboard' MS has), will eventually 'lock' the db (since users typically always leave the Main Menu open for a while) but an unbound form the user leaves open, can be left open all day.

    Make your life much easier (and coding faster) with linked tables and then use a combination of bound/unbound forms for those forms that you anticipate the user will leave open or will do their thing and close quickly. Subforms are typically not a problem but should again, be looked at on how long the user might keep them open. For these cases, I'll make a popup form which updates and closes after a certain time.

    Note also: the vb cloning script in the code bank will also make your life much easier since each user is in their own mdb and thus, prevents locking of the mdb for other users.
    Last edited by pkstormy; 03-18-10 at 21:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks pkstormy

    Your great advices are always benifiting me. I really appreciate.

    I will still wait you to upload a sample with very few unbound fields (let us say 2 or 3 fields) in subform and and few unbound fields on main form. Just only one form that get connected to server (no linked tbls) with vba code.

    I appreciate.

    Sindho,

    I tried to create db property as you said executing Public Function CreateDBProperty in the module 'Mod_Properties'

    Now it produces below:

    Run-time error '3367'
    Cannot append. An object with that name already exist in the collection.


    and halts here dbs.Properties.Append prp in module Mod_Properties.

    Please advise.
    Last edited by Ashfaque; 03-21-10 at 03:14.
    With kind regards,
    Ashfaque

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If both JET_Server_Connect and ODBC_Server_Connnect properties already exist for the database, then you can edit them using the F_Connection form. You can list the existing properties of a database with the following code:
    Code:
    Function ListDBProperties()
    
        Dim dbs As DAO.Database
        Dim pty As DAO.Property
        
        On Error Resume Next
        Set dbs = CurrentDb
        For Each pty In dbs.Properties
            Debug.Print pty.Name,
            Debug.Print pty.Value
            If Err.Number Then
                Err.Clear
                Debug.Print "<Not available>"
            End If
        Next
        Set dbs = Nothing
        
    End Function
    Here is an example of what I get:
    Code:
    Name          C:\Documents and Settings\SinnDHo\Mes documents\Access\CF_Sit\CF_Sit_2\Order_Details\Order_Details.mdb
    Connect       
    Transactions  True
    Updatable     True
    CollatingOrder               1036 
    QueryTimeout   60 
    Version       4.0
    RecordsAffected              0 
    ReplicaID     
    DesignMasterID              
    Connection    <Not available>
    ANSI Query Mode              0 
    AccessVersion 08.50
    ProjVer        35 
    Build          966 
    Jet_Server_Connect          driver={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes
    ODBC_Server_Connect         ODBC;driver={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes
    VersionMajor  1
    VersionMinor  0
    VersionRevision             0
    VersionDate   2010-03-18
    DefaultBackupLocation
    Have a nice day!

  14. #14
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    here is mine in immediate window

    Name D:\ALRASASIA\AlRasasia_Server.mdb
    Connect
    Transactions True
    Updatable True
    CollatingOrder 1033
    QueryTimeout 60
    Version 4.0
    RecordsAffected 0
    ReplicaID
    DesignMasterID
    Connection <Not available>
    ANSI Query Mode 0
    Themed Form Controls 1
    AccessVersion 09.50
    Build 566
    ProjVer 35
    Auto Compact 1
    Show Values Limit 1000
    Show Values in Indexed 1
    Show Values in Non-Indexed 1
    Show Values in Remote 0
    Show Values in Snapshot 1
    Show Values in Server 0
    Use Default Page Folder 0
    Use Default Connection File 0
    Row Limit 10000
    StartUpForm Form.FrmLogin
    StartUpShowDBWindow False
    StartUpShowStatusBar False
    AllowShortcutMenus False
    AllowFullMenus False
    AllowBuiltInToolbars False
    AllowToolbarChanges False
    AllowSpecialKeys False
    UseAppIconForFrmRpt False
    Log Name AutoCorrect Changes 1
    AllowByPassKey True
    ODDC_Server_Connect <Connection string>
    Name D:\ALRASASIA\AlRasasia_Server.mdb
    Connect
    Transactions True
    Updatable True
    CollatingOrder 1033
    QueryTimeout 60
    Version 4.0
    RecordsAffected 0
    ReplicaID
    DesignMasterID
    Connection


    Where the function ListDBProperties you want me to place and run? I tried placed it on open event and watched in immediate window and above deails appeared.

    Thanks,
    Last edited by Ashfaque; 03-20-10 at 07:21.
    With kind regards,
    Ashfaque

  15. #15
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397


    Also it produce below error.

    Run-time error '3251'
    Operation is not supported for this type of object

    and halts here Debug.Print pty.Value in ListDBProperties function
    With kind regards,
    Ashfaque

Posting Permissions

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