Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2009
    Posts
    85

    Unanswered: Linking table to outside source

    Hello, the company I work for is upgrading there main data base system and it turns out there might be a possibility to link to it, I say possible because no one on our end ( IT people included) have any idea what it means to link a table in access to another table anyware, this would be a boon to me and our department as we could then populate all of our tables in Access from the mother load, in stead of scrubbing data out of a Acrobat program. I had no idea this might become possible and am being asked to come up with a list of questions that can be given to our data systems provider telling them what we need from them with regards to linking my access data base to them. Will start studying this right away. To those who link there access to external source's I was hoping you could suggest question's that need to be asked, this will also focus my studies and improve my learning cure, with regards and thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It depends on the main database system and if an ODBC driver is available for it. If such a driver is available, the process on linking Access to such a system should be rather easy. If not, it would still be possible provided you can export the data into a format that Access can use (it could be a text file in csv format for instance), but it would be more complex to manage.

    Most database systems provide an ODBC driver but you sometimes have to by it while some others are free or already provided with the MDAC (Microsoft Data Access Components) in Windows.

    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there may also be another issue whihc is whether the 'other' company will want or be prepared to have a permanent connection from an Access applciation.

    depending on the volatility of that data it may be more sensible to take a dump of that data, say over night, into you access application then there are no constraints or potential problems with your users interfering with "their" database. they amy be very waru of allowing unrestricted access to their data.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2009
    Posts
    85
    Shinndho, healdem, Thank you for your comments both very helpful, I am reading up on ODBC connections.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by healdem
    there may also be another issue whihc is whether the 'other' company will want or be prepared to have a permanent connection from an Access applciation.

    depending on the volatility of that data it may be more sensible to take a dump of that data, say over night, into you access application then there are no constraints or potential problems with your users interfering with "their" database. they amy be very waru of allowing unrestricted access to their data.
    I actually prefer an intermediate transport method even when I have full control over BOTH sides of the pipeline. There is something to be said for the flexibility gained by your application not knowing nor caring how the information its working with was generated. Assuming you're working with a self-contained application platform like Access and you don't need real-time views to the live data, there's no real reason to maintain a persistent (and brittle) connection to third party datasources. I'm sure that has more to do with my development background and tendency to lean towards SOA design queues than it does my DBA experience though...

    Also, experience tells me that forcing small, bespoke Access databases to be dependent on the persistent availability of a large business intelligence store that you have no control over will only end in broken hearts.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There is no need to maintain a permanent connection with the data, even if you use ODBC as data transport. You can create the link when you need it, for the time you need it, then close it so that no permanent link is kept with the data source. E.g., you can create the link, import the data into local Access tables, close the link then perform whatever action you want with the imported data in Access.

    Here is an example (for SQL Server in this case):
    Code:
    Private Const connection_string = "ODBC;driver={sql Server};SERVER={Server};DATABASE={Database};Trusted_Connection=Yes;"
    Private Const System_Tables = "sys_columns,sys_default_constraints,sys_extended_properties,sys_objects,sys_tables"
    
    Public Function GetMetadataFromServer(ByVal ServerName As String, ByVal DatabaseName As String, ByVal Persistent As Boolean) As Long
    
        On Error GoTo Err_GetMetadataFromServer
        
        Dim varTables As Variant
        Dim varTable As Variant
        
        LogHandler.Log "Retrieving data from server.", " GetMetadataFromServer", , log_Info, _
            "Server: " & ServerName & ";Database: " & DatabaseName & ";Keep tables: " & Persistent
        varTables = Split(System_Tables, ",")
        For Each varTable In varTables
            If AttachServerTable(ServerName, DatabaseName, CStr(varTable)) <> True Then
                GetMetadataFromServer = False
                Exit Function
            End If
        Next varTable
        CurrentDb.Execute "Qry_DELETE_FROM_Tbl_Server_Metadata"
        CurrentDb.Execute "Qry_INSERT_INTO_Tbl_Server_Metadata"
        CurrentDb.Execute "Qry_UPDATE_Tbl_Server_Metadata"
        If Persistent = False Then
            For Each varTable In varTables
                DoCmd.DeleteObject acTable, CStr(varTable)
            Next varTable
        End If
        GetMetadataFromServer = True
        
    Exit_GetMetadataFromServer:
        Exit Function
        
    Err_GetMetadataFromServer:
        LogHandler.Log Err.Description, , Err.Number, log_Error_3
        GetMetadataFromServer = Err.Number
        Err.Clear
        Resume Exit_GetMetadataFromServer
        
    End Function
    
    Private Function AttachServerTable(ByVal ServerName As String, ByVal DatabaseName As String, ByVal TableName As String) As Long
    
        On Error GoTo Err_AttachServerTable
        
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strCnn As String
        
        strCnn = Replace(connection_string, "{Server}", ServerName)
        strCnn = Replace(strCnn, "{Database}", DatabaseName)
        If Not IsNull(DLookup("Type", "MSysObjects", "Name = '" & TableName & "'")) Then
            DoCmd.DeleteObject acTable, TableName
        End If
        Set dbs = CurrentDb
        With dbs
            Set tdf = .CreateTableDef()
            With tdf
                .name = TableName
                .SourceTableName = Replace(TableName, "_", ".", , 1)
                .Connect = strCnn
            End With
            .TableDefs.Append tdf
            .Close
        End With
        AttachServerTable = True
        
    Exit_AttachServerTable:
        Set tdf = Nothing
        Set dbs = Nothing
        Exit Function
        
    Err_AttachServerTable:
        AttachServerTable = Err.Number
        Err.Clear
        Resume Exit_AttachServerTable
        
    End Function
    Have a nice day!

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Now you're tied to SQL Server.

    I would much rather be looking at a txt/csv/what-have-you that was dropped by SQL Server than SQL Server itself. UNLESS there is a good reason for real-time data. That way if the datasource has to change, the Access application doesn't know nor care.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's the way I used to work... in 1985.

    Have a nice day!

  9. #9
    Join Date
    Apr 2009
    Posts
    85
    Thanks for this discussion I very much appreciate it, its doing what I hoped it would do, generate lots of questions in my head, one of these questions is, I will need to send data as well, this is a big part of what will be happening, if the link is not live can you(and is it a common process) do a reverse data dump i.e update the tables at the the provider source, with the data one has collected.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sending data back to a server after having extracted them is a different matter, and there is no easy answer.

    If you use an ODBC driver, this one usually authorizes you to perform the four basic operations (SELECT, INSERT, UPDATE, DELETE), however you might violate data integrity rules when trying to do so.

    In such situation, the best result is that the server refuses to perform the operation and hopefully generates a more or less meaningful error code that is sent back to the calling procedure. In worst scenarios you make a mess into the data.

    My best answer, which is not a very satisfactory one, is: it depends on how well you know the data protection mechanisms of the server, how well you know how the server will react in case of attempting to do something that violates the data integrity rules (if any!) of the database, and how well you know about the data organisation and structure of the database in the server.

    When I work with databases on a SQL Server I do not hezitate a lot, mainly because I know MS SQL Server rather well and because I usually work with database that I created or that I administrate. Being in front of an unknown database, possibly on an unknown server, my reaction would be far more cautious.

    As a first approach I probably would try to either generate an intermediary file (or set of files), possibly in csv or any other convenient format, and have the server to import that (those) file(s) itself, or, if injecting data directly back to the server though ODBC, i would try to place the data into temporary or buffer tables and have the server process those tables.

    In any case, you have to take in consideration that some priority mechanisms must be set, specially if you work in a multi-user environment. What happens when you send data back to the server and those data were modified by another user/process after you exported them?

    Have a nice day!

  11. #11
    Join Date
    Apr 2009
    Posts
    85
    Sinndho, thank you for all your comment's and the code, it has provided a lot food for thought and eased a way some of my naivety around ODBC connections. Tim

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome.

    Have a nice day!

Posting Permissions

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