ADO Import 1

[luceze]

I have a SQL Server table that I am linking to via ADO. I have to use ADO because the table has 500+ fields. I have a SQL string that selects the 162 fields that I need. What I now need to do is import the data into an Access table that I can use as the record source for a report. (Access won't let me set the recordsource of the report to a ADO recordset.) I guess that I could loop through all the records and fields and add\update to the Access table but I was hoping for a faster approach. Any and all suggestions will be appreciated.

Thanks,
Eric

 

[cmmrfrds]

You cannot set the recordsource to a recordset, but Access 2000 and above has a recordset object on the Form. You can set the recordset to the Form recordset. What version?

 

[luceze]

Thanks for the response. I'm using 2002. The form isn't really a problem. (I'm just populating a listview control) It is the report that is the problem. Access will not allow you to set the recordset of a report object, hence the need for a temp table.
Right now I am populating the temp table by looping through the recordset. However it takes around 4 minutes to fill the table and more records will be added each day. You can see my dilemma. By the way I've looked through several of your ADO posts and found them quite helpful in the past.

Thanks,
Eric

 

[cmmrfrds]

Can you use an ODBC linked table instead? That way the table could be the record source, or a pass-thru query based on the ODBC connection could be used.

 

[luceze]

I can't link via ODBC. It will only grab the first 256 fields.
What is a pass through query?

Thanks,
Eric

 

[cmmrfrds]

Pass thru is one of the options under other sql in the query grid. You will still need an odbc connection but pass your sql string through instead of linking the table.

 

[luceze]

You rock!!! That was so simple and fast. Thanks for all of your help.

Thanks,
Eric