Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005

    Unanswered: Referencing other databases in SQL Statements


    I have an access database and an SQL database and using data transformation services, i want to update the access database using the SQL data.

    Can anyone tell me the syntax for referencing the access database?

    Is it something like: [TABLENAME].dbo.FIELDNAME ?

    Just to clarify, i have

    Microsoft Access Database
    Table 1 (UnitHistory)

    SQL Database
    Table 1 (UnitHistory)

    How do i reference these seperately? I want to update the microsoft access database based on the SQL database data.

    Eventually i'm trying to update an access database using the data held on my SQL server. Is DTS the best way for me to acomplish this or should i use another method?

    Thanks guys
    Last edited by r_e_v_a_n_s; 10-17-05 at 06:01.

  2. #2
    Join Date
    May 2002
    1. look up in Sql book online for the syntax to create a linked server to Access.
    2. do something like this to update Access:

    Update A
    set UnitHistory = S.UnitHistory
    from [Access_linkedserver]...[Tb_name] A, [Sql_Tb_Name] S
    where A.pkid=S.pkid
    -- and

Posting Permissions

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