Results 1 to 8 of 8
  1. #1
    kummakki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    4

    how to select data from SQL Server into access table

    Hi,



    could you tell me the code how I could fetch some data from SQL server table into Access table.

    Connections are ok, that is not a problem. The issue is that I don't know how to actually put the data into the Access table.

    SQL = "INSERT INTO Table (Name) SELECT ac_name From Account where ac_name = '11111'"

    the Table is the Access table and the account table is in the SQL Server. How I should do this to make it work...?

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If the data is contained in a SQL table, you can just link that SQL table into the Access Table Object section. Just go to Import External data, choose other, choose the connection type (I usually use ODBC), make sure it is sent to Link instead of direct Import, and Finish.
    Then, you can write queries on this table just the same as you would with any other Access table.

    Alternatively, you can just create a Pass-Through query in Access to fetch the data for the criteria you want directly from the SQL table. You would set the connection information in the Query properties, and paste the SQL code in the SQL View option of the query.
    Here are some instructions on how to do that: http://support.microsoft.com/kb/303968

  3. #3
    kummakki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    4
    Do you know how I do it using VBA? my query has multiple joins...

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Do you know how I do it using VBA? my query has multiple joins...
    I am not sure I understand your direction. Why would the number of joins have any affect on how you do the query or necessitate the use of VBA?

  5. #5
    kummakki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    4
    That was just a comment . Anyways I'm trying to do a macro that does some other stuff also. I would like to find the vba code for insert into access table from sql server.

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Usually, you use VBA for repetitive tasks or tasks that you want to do "on-the-fly" because of changing criteria. I don't see either of those situations here.
    Once you set it up, you shouldn't need to edit or modify it, so I do not see any advantage to using VBA.

    Am I missing something?

  7. #7
    kummakki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    4
    i just want to know how to add data with the sql server select clause to access table insert into clause. do you know the answer for that one?

    and yes, there is a reason why i want to do it this way

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I have never had a need to ever do anything like that myself (and am not entirely convinced that is the way you need to go either, but since you haven't given us a full understanding of why you feel you need to do this, it is hard to offer other solutions to), but a basic Google search returned some threads that may help you to do that.
    See:
    http://support.microsoft.com/kb/892490
    http://bytes.com/topic/access/answer...hrough-vb-code
    http://stackoverflow.com/questions/1...erver-with-vba

    By the way, if you link to your SQL table, you do not need to Insert the data into an Access table to make use of it (unless your final goal is to import data into your Access table). You can access/use the data directly from the linked SQL table in your queries and other processes.

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

Similar Threads

  1. Replies: 1
    Last Post: 10-22-2012, 06:44 PM
  2. Data access pages and SQL Server
    By jfloan in forum Access
    Replies: 0
    Last Post: 03-19-2012, 01:39 PM
  3. Replies: 3
    Last Post: 01-03-2012, 12:28 PM
  4. Select Data from SQL into an Access table
    By MichaelC in forum Programming
    Replies: 4
    Last Post: 07-28-2010, 04:10 PM
  5. Replies: 1
    Last Post: 05-13-2010, 10:37 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
  •  
Other Forums: Microsoft Office Forums