Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    Oakland, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using DAO to link: SourceTableName?

    Puzzled about SourceTableName... The database I'm working on will need to allow the user to import new data (which we get as an Excel 97 spreadsheet) on a regular basis. In order to avoid the bloat that would be caused by creating and deleting a temporary table, I'd like to just link to the spreadsheet and then use RecordSet methods to step through it and import the records.

    The online help says that using DoCmd.TransferSpreadsheet with transfertype=acLink is not recommended, and that using DAO (specifically the Connect property for a TableDef object) is preferred. Can anyone provide a working example of how to do it? I'm confused about the SourceTableName property in particular -- should that be set to the name of the worksheet within the Excel file? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: using DAO to link: SourceTableName?

    You didn't indicate what version of Access you're using, and it does make a difference, since the methods and properties exposed have changed over the versions. Here's code that will work with at least 97 and later.

    <pre>Private Function LinkTable(ByVal strTblName As String, _
    ByVal strTblAlias As String, _
    ByVal strConnect As String)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef

    On Error GoTo HandleErr
    Set dbs = CurrentDb()
    Set tdf = dbs.CreateTableDef(strTblAlias)
    With tdf
    .Connect = strConnect
    .SourceTableName = strTblName
    End With
    dbs.TableDefs.Append tdf
    LinkTable = tdf.Name
    Set tdf = Nothing
    Set dbs = Nothing
    ProcExit:
    Exit Function
    HandleErr:
    Select Case Err.Number
    Case Else
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbCritical, "LinkTable"
    End Select
    Resume
    End Function</pre>


    Connect is the connection string, including the full path and filename of the target file. If you manually link an Excel file to a database,then you can look at the "table" design and you'll find both the Connect string and the sourcetablename in the description. SourceTableName is the name of the table in an mdb or the worksheet or range in an excel spreadsheet. Here's an example of an Excel description:

    Excel 5.0;HDR=NO;IMEX=2;DATABASE=D:Excel DocsSAMPLES.XLS;TABLE='Data Validation$'

    The first part, up to TABLE, is the connect string. The last part, without the "TABLE=", is the SourceTableName. Using the above to call LinkTable would look like this:

    linktable("Data Validation$","DataValidation","Excel 5.0;HDR=NO;IMEX=2;DATABASE=D:Excel DocsSAMPLES.XLS;")

    Does that help?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Location
    Oakland, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using DAO to link: SourceTableName? (A97 SR2)

    Sorry, I thought I'd entered the version info. Thank you for the code -- it's working nicely, and the links are linking just as they ought to.

    Now on to the next challenge--actually working with the tables now that they're linked. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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