Results 1 to 4 of 4
  1. #1
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    84

    sql server query to local table

    Hi.



    I have an adodb connection to an sql server. I execute sql and get a recordset. I would like to write that recordset to a local Access table in the current access database...
    1) I am having problems writing the ADO recordset to a table. Any help??
    2) Is there anyway I can use an sql "into" a local table that does not exist??? like: Select [Project Number] into Local_Table from Raw_Data_SV. It seems that when I put the "into" the execute tries to create the table on the server and not locally. I want it locally.
    Code:
    Public Sub Make_TEP_Table(tablename As String, sql_txt As String)
    
    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    
    
        cn.Provider = "sqloledb.1"
        cn.Properties("Data Source").Value = "XXXXXXXX"
        cn.Properties("Initial Catalog").Value = "IDB"
        cn.Properties("Integrated Security").Value = "SSPI"
        cn.Open
        
        sql_txt = "Select [Project Number] from [Raw_Data_SV]"
        Set rs = cn.Execute(sql_txt)
        rs.MoveFirst
    Thanks.
    Steve
    Harrisburg, PA

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    What I usually do is loop through the records and append as I iterate. I am not remembering a method to update all the records in a recordset to a local table. So you could opena second recordset or execute some SQL.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Did you try using the "externaldatabase" argument?

    Code:
    SELECT field1[, field2[, …]] INTO newtable [IN  externaldatabase]     FROM source
    I found this in Access Help....

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by ssanfu View Post
    Did you try using the "externaldatabase" argument?
    ...
    That's an idea.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-15-2014, 12:41 PM
  2. Inserting Data to Access Table (Local) from MySQL table (Net)
    By gambit1430 in forum Import/Export Data
    Replies: 2
    Last Post: 09-26-2014, 01:34 AM
  3. Replies: 23
    Last Post: 07-09-2014, 02:16 PM
  4. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  5. Replies: 1
    Last Post: 10-15-2012, 02:41 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