Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2014
    Posts
    2

    Unanswered: Linking new database to old one

    Hey there guys,

    I am new to this whole database thing and am trying to figure it out as I go along by reading and watching videos.

    Background:
    I am trying to create a database for an NGO I am working for which will record answers from a questionarre that students must fill out before and after the completion of our course.

    Issue:
    OK so before I started here another individual created an excel document and filled in about 600 questionarres. I obviously don't want to go back and write every one out again and so am looking for a way for the new database to retreave the information on the old one. (some form of relationship). What I am struggling to understand is I get how to create relationships if the data is the same between two fields, but what if I want to retrieve all the names from my first list? none of the names are the same obviously.

    Hope that makes sense.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this an Excel question, an Access Question
    are you planning on importing the db into Access?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In MS Access you have two options:
    1. You create linked (attached) tables. This can be done programmatically:
    Code:
    Public Sub AttachTable(ByVal ConnectionString As String, ByVal OriginalName As String, Optional ByVal NewName As String)
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        If NewName = "" Then NewName = OriginalName
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef(NewName)
        tdf.Connect = ConnectionString
        tdf.SourceTableName = OriginalName
        dbs.TableDefs.Append tdf
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Sub
    You can test it with:
    Code:
    Sub TestAttachTable()
    
        AttachTable ";DATABASE=U:\Access\Scoreboard\scoreboard.mdb", "Tbl_Switchboard_Items"
        
    End Sub
    2. You can import the table in the current database:
    Code:
    Sub ImportTable(ByVal DatabaseName As String, ByVal OriginalName As String, Optional ByVal NewName As String)
    
        Dim appAccess As New Access.Application
        
        If NewName = "" Then NewName = OriginalName
        With appAccess
            .OpenCurrentDatabase DatabaseName
            .DoCmd.CopyObject CurrentDb.Name, NewName, acTable, OriginalName
            .CloseCurrentDatabase
            .Quit
        End With
        Set appAccess = Nothing
        
    End Sub
    You can test it with:
    Code:
    Sub TestAttachTable()
    
        ImportTable "U:\Access\Scoreboard\scoreboard.mdb", "Tbl_Switchboard_Items"
        
    End Sub
    Other techniques implying SQL or ODBC sources also exists
    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
  •