Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking SQL tables (VBA Access 97/SR2)

    Short and sweet:
    Is it possible to execute code that makes a link to an SQL table from within an .mde?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking SQL tables (VBA Access 97/SR2)

    In a word: yes.

    You can link tables in an .MDE database the same way you can do it in an .MDB. For example:

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Set dbs = CurrentDb
    Set tdf = dbs.CreateTableDef
    With tdf
    .Name = "MyTable"
    .Connect = "ODBC;Database=MyDatabaseName;DSN=MyDSN;UID=MyUser ;PWD=MyPassword"
    .SourceTableName = "dbo.MyTable"
    End With
    dbs.TableDefs.Append tdf
    Set tdf = Nothing
    Set dbs = Nothing

    Whether you need to supply a user name and password in the Connect property depends on the kind of access you have to the SQLServer database. If you have access with Windows authentication, you can omit the UID and PWD.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking SQL tables (VBA Access 97/SR2)

    Thanks Hans.

    For some reason the way I had it written wasnt working. The code you gave me however, works perfectly. Thanks.

Posting Permissions

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