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

    Unanswered: Referencing other databases in SQL Statements

    Hello,

    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 07:01.

  2. #2
    Join Date
    May 2002
    Posts
    299
    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
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

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