CODEWITHSUNDEEP
sqlsql-servervbams-access
Dr Phil
Dr Phil

Reputation: 807

Fetching selected columns from a wide (> 255 columns) SQL Server table into MS Access

I have some VBA code that analyzes data in MS Access. Some of the data is in a SQL Server (linked within Access).

My issue is that this particular table has too many columns (255+), so Access truncates some of the columns that are relevant to me.

My thought was that I can pull the relevant data (I only need 10 or so columns) using ADO (or DAO), save it in my access table and move on with the rest of the logic.

Dim cn As New ADODB.Connection
Dim ssql as String
cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=CID;Integrated Security=SSPI;"
ssql = "SELECT a.channel, a.Firm_Name, a.[AUM 0915], a.[Rev 0915] INTO [Localtable] FROM dbo.TableName"
cn.execute (ssql)

I could get the Recordset and then loop through it, though not keen on doing that (it's a few hundred thousand rows of data, which makes it really slow). What I am hoping for is a solution that either

  1. (preferably) Does it all in a single SQL statement

  2. (I'll take this) Dumps the whole Recordset into Access (hopefully efficiently)

Appreciate any pointers. Here are my additional constraints

  1. I don't have any say in how the data is stored in SQL Server itself, so I can't ask anyone to reduce the number of columns

  2. I only have read permission on the SQL server, so I can't create a new table on the server and then link it through Access

Thanks.

Upvotes: 1

Views: 770

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123629

Probably the most straightforward way to accomplish your goal would be to create a pass-through query using the SELECT statement cited in the question. The pass-through query is executed on the SQL Server and therefore is not restricted to the first 255 columns of the SQL Server table, as would be the case for an ODBC linked table in Access.

The recordset produced by a pass-through SELECT query will be read-only. If that proved to be inconvenient when using it in other queries (i.e., by tripping over "Query must use an updateable recordset" errors) then you could always use a make-table query to dump the results of the pass-through query into a local Access table:

SELECT * INTO [myLocalTable] FROM [myPassThroughQuery]

Upvotes: 3

Related Questions