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

    create local table from sql server table

    Hi.



    What is the SQL to create a local access table from an SQL Server table?? I only have read access to the sql server. When I "Select * Into MyNewTable from RawData_SV"... It tries to create a table on the SQL Server and I only have read access.

    I have the VBA ADOX connection working and can read the table into a recordset, but I am having problems writing the recordset to a local table (field type / recordsize etc). If I could sql in VBA to a make table it would be a lot easier.

    The problem is that the Server Table has 450 columns (access will only see the first 255)... Our IT dept has an excel file that will dump all 450 columns (so I know the names of each column), but I want to get specific data only - with access and write to a table. Then I could query the table.

    Thanks!
    Steve

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You have a few options

    1. If your it department can export this data to excel you can very likely create some vba to import only the fields you want from each record. You would have to use excel controls to cycle through the excel file which may make it a bit clunky, but then it can be done.
    2. Create two views on the SQL side, you would need to split the table you're interested in into 2 parts of 255 fields or less on each side, make sure to include the field(s) required to correctly link the two parts in each half of the query. Link these two views into your Access database and create your queries based on the bits of each side you want.
    3. Create a view on the SQL side with just the data you're interested in and export that.

    I think you probably want to try and stay away from adding/deleting data from tables if you can if this is a large dataset (hundreds of thousands of records) particularly if there are 450 fields per record.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    As some additional comments ...

    You should be able to use parenthesis to query the data of the SQL server, first. Then, use those results in an append query. The easiest would be to create a Query Object that retrieves the columns, data, etc. that you want. Save that query as a standalone SELECT query Object. Then, create a new append query using the previous query object.

  4. #4
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    84
    Maybe I did not explain well enough... I wont "Select *", I will Select [ProjectID], [Date], [Capital Required] into MyNewTable from [RawData_SV]... What is the sql (that I will execute from VBA) to have a local table created from the ADOX connection??

    This way, I dont have to have an excel file refresh and linked and split etc. I just want to get specific records from the server (Not all 450).
    I cannot create anything on the Server side
    The table on the server is about 10,000 lines long
    Thanks

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You will not be able to link or manipulate a query or table from SQL that has more than 255 fields. If you do not have access to the server and your IT staff will not create a view you can use (a view in SQL can be used like a table in MS access) you will have to write code to manipulate an excel file export. There is no way I know of around the 255 field limit so, based on what you're saying, it doesn't matter a whit that you want to import data to a local table, the problem is the field limit which you have to work around.

    Again, if your IT staff won't help you, you'll have to write some VBA to parse an excel file for the fields you want (which isn't too hard), you could also parse a text file as long as the delimiters were clearly defined (i.e. comma delimited but no commas in the content of any field). The text file would likely be easier if you are a relative neophyte.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by rpeare View Post
    You will not be able to link or manipulate a query or table from SQL that has more than 255 fields...
    Hmmm, never tried that one before. Is the only way to have a view with less than 256 columns? What about a pass through query that only defines the columns desired? That would be a similar thing. Only, this way, no need to have special permissions on the DB. You would just need a connection string.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    the query would only work if the fields he was after were in the first 255 columns I think, It may be possible to link the table/view if it has more than 255 fields but you'd only see the first half of the table. I haven't attempted to do something like this in a very long time but if I remember correctly I couldn't even link the table at the time so I had to split it. If it's now possible to link a larger table and get the first 255 fields I do not think you'd be able to return column 256 in a pass through query. Are you suggesting passing a query from access to SQL and running the pass through on the SQL side?

  8. #8
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    84
    I can "Select [TimeStamp] from RawData_SV... TimeStamp is column# 450... So, I can vba sql to get any combination of columns I want without linking anything. the 255 limit does not effect me (as long as I get <256 columns). Say, I want to get column 2, 260, 400, 450, I can get it with sql. So, what is the SQL to write to a local table and not to the sql server? The code below connects, executes. I want to an an INTO to send the recordset to a local table
    Code:
    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    cn.Provider = "sqloledb.1"cn.Properties("Data Source").Value = "xxxxxxxxcom"
    cn.Properties("Initial Catalog").Value = "IDB"
    cn.Properties("Integrated Security").Value = "SSPI"
    cn.Open
    sql_txt = "select [TimeStamp] from [Raw_Data_SV]"
    Set rs = cn.Execute(sql_txt)
    Last edited by SteveApa; 09-12-2016 at 09:44 AM. Reason: Adding declarations to code

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by rpeare View Post
    ... Are you suggesting passing a query from access to SQL and running the pass through on the SQL side?
    I was thinking of executing the SQL on SQL Server via a pass through query. Then, with that SELECT statement (passthrough query), append records using the passthrough as an object that is referenced in an Access Append query object.

    Having said that, might as well loop through the ADO Recordset SteveApa has created.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    ok I misunderstood where you were in your problem.

    As long as you are aware that the changes you make will not change your source data (SQL side) perhaps the easiest thing to do is to create a table with structure you want then run the INSERT INTO query, and if this is a cumulative set of information every time you will likely want to delete data from your 'temp' table prior to each addition of data.

    I.e.

    currentdb.execute ("DELETE * FROM tblTemp")
    sql_txt = "INSERT INTO Table3 (TestField) SELECT TestField FROM [Raw_Data_SV];
    currentdb.execute (sql_txt)

    If this doesn't work (I'm not sure it will executing in a string as opposed to opening a recordset the way you have it), you should alternately be able to cycle through the recordset and generate a SQL statement to append the data to the table on a record by record basis though this might be slower.

    if your target table will have a variable number of fields that's a little more complex

  11. #11
    SteveApa is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    84
    YEAA!!! I have never used a Pass Through Query. Worked like a Charm... I now don't need to VBA at all, Don't need to make a local table... Just used "select [NPS Contribution] from [Raw_Data_SV]" in a pass through and it returned the data. Remember, [NPS Contribution] is column 450!!

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Good feedback. Thanks for the update.

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

Similar Threads

  1. sql server query to local table
    By SteveApa in forum SQL Server
    Replies: 3
    Last Post: 09-08-2016, 01:31 PM
  2. Replies: 1
    Last Post: 07-05-2016, 07:42 AM
  3. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  4. Replies: 8
    Last Post: 01-29-2016, 03:16 PM
  5. How to Create local copy of linked Table
    By behnam in forum Programming
    Replies: 3
    Last Post: 11-20-2014, 05:49 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