Results 1 to 2 of 2
  1. #1
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    84

    Eliminate SQL server via ODBC and use vb connection properties in table connection

    Hi.
    I have a Globally used Access DB. It has linked sharepoint tables, local tables, and linked SQL Server tables. The SQL Server tables are linked with ODBC. But, everyone in my company does not have the ODBC connection setup on their system. I don't want everyone to need to setup the ODBC on their system (people are lazy and won't do it). So, I created the connection in VB to the SQL server linked table and re-write it to a local table and then query off of that. But, it is slower than if they did have the ODBC. Code below:
    Code:
    Public Sub Update_Raw_Data_ADO()
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim db As Object
        Dim rs_local As Object
        Dim variable_name As String
        Dim sqltxt As String
        
        '--- create connection to sql server table and open connection -----
        cn.Provider = "sqloledb.1"
        cn.Properties("Data Source").Value = "xxxxxxx.com"
        cn.Properties("Initial Catalog").Value = "IDB"
        cn.Properties("Integrated Security").Value = "SSPI"
        cn.Open
        
        '--- run sql against linked table -----
        sqltxt = "SELECT (bla bla bla)"
        Set rs = cn.Execute(sqltxt)
        rs.MoveFirst
        DoCmd.SetWarnings False
        
        '--- delete local table contents -----
        DoCmd.RunSQL "DELETE * FROM " & "[9c1) Raw Data Table Items Pass Thru - Local Qry]"
        DoCmd.SetWarnings True
        
        '--- Open local table and fill it with linked table -----
        Set db = CurrentDb()
        Set rs_local = db.OpenRecordset("9c1) Raw Data Table Items Pass Thru - Local Qry")
        While Not rs.EOF
            rs_local.AddNew
            For i = 0 To rs.Fields.Count - 1
                variable_name = rs(i).Name
                rs_local(variable_name).Value = rs(variable_name).Value
            Next i
            rs_local.Update
            rs.MoveNext
        Wend
        rs.Close
        rs_local.Close
        cn.Close
        Set rs = Nothing
        Set rs_local = Nothing
        Set cn = Nothing
    End Sub
    Is there any way to put this vb connection info in the "Properties" of the linked table so they don't need the ODBC and not do it in VB and just do it in access queries???

    Thanks.


    Steve
    Harrisburg, PA

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might read about "DNS-less Connections".

    ODBC DSN-Less Connection Tutorial https://www.databasejournal.com/feat...n-Tutorial.htm
    What is a DNS-Less connection? https://answers.yahoo.com/question/i...J&guccounter=1
    Using DSN-Less Connections http://www.accessmvp.com/DJSteele/DSNLessLinks.html
    The Connection Strings Reference https://www.connectionstrings.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  2. Replies: 5
    Last Post: 12-16-2015, 02:23 PM
  3. Connection to SQL Server ODBC Failed
    By Historypaul in forum SQL Server
    Replies: 2
    Last Post: 01-05-2015, 01:50 PM
  4. Replies: 1
    Last Post: 05-17-2012, 05:51 PM
  5. Replies: 0
    Last Post: 03-08-2012, 03:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums