Results 1 to 5 of 5
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Amp within code - download data from MS SQL Server


    Hi Guys,

    I am trying to figure out how to download data from MS SQL Server to MS Access FE to local table.

    I have found the code here:

    https://accessexperts.com/blog/2011/...easy-analysis/

    code is:
    Code:
    Private Sub TestTemp()   On Error GoTo ErrorHandler
       Dim strSQL As String
       Dim strTable As String
       strTable = "tblTempTest"
       'Delete the table if it exists
       DoCmd.DeleteObject acTable, strTable
    strSQL = "Select * INTO " & strTable & " FROM tblCustomers " & _
    "Where CustomerState = 'ILL'"
    Currentdb.Execute strSQL
    'Insert more code here to do something with temp table
    Exit Sub
    ErrorHandler:
    If Err.Number = 7874 Then
    Resume Next 'Tried to delete a non-existing table, resume
    End If
    End Sub
    But when i am trying to use it with linked table there is an error connected with "&amp" code. what is this and how can i use it in VBA?

    thank you for helping,
    Best Wishes,
    Jacek

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    link the SQL table into your db,
    then run an append query to copy the data.
    no code needed.

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you ranman256!

    I know and your approach is very good.
    But i want to know all aspected and methods...

    Thank you once again my Friend,
    Jacek

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    strSQL = "Select * INTO [" & strTable & "] FROM tblCustomers"

    ( * only works if both tables are equal, otherwise you must do it field by field)

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The & is a display error (due to bad HTML code) in that web page.

    The line should be:

    strSQL = "SELECT * INTO " & strTable & " FROM tblCustomers WHERE CustomerState = 'ILL'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-10-2016, 03:20 AM
  2. Download data from FTP to MS Access
    By vlad in forum Import/Export Data
    Replies: 3
    Last Post: 03-24-2015, 01:35 PM
  3. Replies: 5
    Last Post: 07-17-2011, 08:57 AM
  4. Replies: 1
    Last Post: 07-17-2010, 08:29 PM
  5. Replies: 0
    Last Post: 03-09-2009, 12:20 PM

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