Results 1 to 12 of 12

SQL Server import using Access

  1. #1
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    397

    SQL Server import using Access


    I regularly run an import process within SQL Server. Is there any way to execute this import through Access? I'm familiar with using pass through queries. The data that is being imported, is from another SQL server. Thank you in advance!!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    4,899
    you could use Access to pull data from Sql server1
    and append to table in Sql server2.

  3. #3
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    397
    By pulling the data from server1 into access, then appending from access to server2. There's no way to use the SQL import process?

  4. #4
    ssanfu is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,262
    I regularly run an import process within SQL Server.
    How are you doing this? Executing a SP?

    If yes, maybe this will help:
    How to Execute SQL Stored Procedures from Microsoft Access
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    397
    I'm using the SQL Server import wizard. The primary reason is the datasource is located on Server A, and the destination is located on Server B. I'm unable to setup a linked server. If a linked server could be setup, I could do a simple append query.
    Can a stored procedure use a second server as the datasource?

  6. #6
    ssanfu is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,262
    I am way over my head right now. I've started using SQL Server this year. but at the basic level.

    One thought..... it seems you have read privileges from the source SQL Server dB and read/write privileges to the destination SQL Server dB.
    In Access, could you not set up two ODBC connections (ODBC_Source and ODBC_Dest), link the two dB's (the tables) and use an append query to import the data?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    ssnafu is correct, set up both as linked tables and run a simple append query from read table to write table. The bigger question is why is this being done in access? TBH, this type of process should be done all within Sql Server framework (Sql Agent or SSIS....if you are not using the data in the access db for anything why is it in there?).

  8. #8
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    397
    Within the network that I exist, not all users have SSIS, but everyone has Access. When I try to append a linked table that resides within an SQL DB, the append query takes a long time. When I use the SQL import wizard it is very quick. I know that I can execute the wizard from a command prompt, but I don't know how to enter the attributes, server name, query, etc.

  9. #9
    aytee111 is online now Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,304
    If you could create the query in the second SQL Server then you could copy it to Access as a pass-thru query. That would be a question for SQL Server experts!

  10. #10
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    397
    With the two servers not being linked, how do I run a query on one server, using the data on the second server?

  11. #11
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    16,364
    When you say not linked, do you mean to Access or to each other? If the second is set up as a linked server in SQL Server, you can use the 4 part naming:

    SELECT Blah
    FROM ServerName.DatabaseName.dbo.TableName
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  12. #12
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    397
    Within SQL, I'm not able to link the two servers.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to import data from Excel to SQL Server
    By adnancanada in forum Import/Export Data
    Replies: 1
    Last Post: 02-09-2016, 08:26 PM
  2. Import excel data to sql server programatically using ms access
    By selvakumar.arc in forum Import/Export Data
    Replies: 3
    Last Post: 04-29-2013, 07:32 AM
  3. Import .TXT file into SQL Server 2005 Table
    By taimysho0 in forum SQL Server
    Replies: 1
    Last Post: 05-23-2012, 02:02 PM
  4. Import into SQL Server from Access Error
    By kaledev in forum Access
    Replies: 1
    Last Post: 02-16-2011, 02:43 PM
  5. Import from SQl server
    By NaatuGaadu in forum Import/Export Data
    Replies: 2
    Last Post: 06-18-2009, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums