Unanswered: Export SQL server query data to Access database
First, i am sory about my english.
I have such situation. In my project as database server we have SQL 2000 and as client applications we use Access Project. (adp)
In this project we have one form where users select data from different tables from server. As result form generate SQL query.
For example one very simple generated query look like:
case when ac.record_id is null then '' when ac.flag_code=1 then ac.code + ISNULL(val.mark,'') else '' end AS Code,
case when ac.record_id is null then '' when ac.flag_name=1 then ac.name end + case when ac.flag_unit = 1 then ' ('+ac.measure+')' else '' end AS CName,
val.v1, val.v2, val.v3, val.v4, r1.Nos AS Nos1
FROM #RawVal153 val
LEFT OUTER JOIN (SELECT a.record_id, a.code, a.name, a.measure, b.flag_code, b.flag_unit, b.flag_name
FROM MetaDataBase.dbo.me_attrib_classif_codes a
JOIN MetaDataBase.dbo.me_attrib_classificators b ON (a.classif_id = b.classif_id)) ac ON ((ac.record_id = val.record_id))
LEFT OUTER JOIN (SELECT resp.uuk, resp.Nos
FROM mi_respondents resp
INNER JOIN mi_resp_quests rq ON (resp.resp_id=rq.resp_id)
INNER JOIN mi_survey_instances i ON (rq.instance_id=i.instance_id)
WHERE (i.version_id = 67 and i.period = '2001')) r1 ON (r1.uuk = val.uuk)
ORDER BY val.uuk,
case when ac.record_id is null then '' when ac.flag_code=1 then ac.code + ISNULL(val.mark,'') else '' end,
case when ac.record_id is null then '' when ac.flag_name=1 then ac.name end + case when ac.flag_unit = 1 then ' ('+ac.measure+')' else '' end
And the fragment from application Where we use this query is:
Dim comXL As ADODB.Command
Dim RecV As ADODB.Recordset
.... 'There ir code, where create TMP tables and generate final SQL query
Set comXL = New ADODB.Command
comXL.CommandTimeout = 0
comXL.CommandType = adCmdText
comXL.CommandText = "Generated SQL query"
comXL.ActiveConnection = Get_Conn() ' function which returns connection variable to SQL server
Set recV = comXL.Execute()
There is no problem to export this recordset to Excel - all is very simple and quite fast. And i don't need to know anything about columns count and data types in my recordset.
But now we need to export this recordset to Access database (*.mdb)
And I need help. May be someone have any ideas or experience in such situation.
when you say exporting to Excel I doubt that you are creating an XLS file - I assume you are exporting to text - if so excel will read that without a hitch - but I do not believe there is an option to export to MDB format per se - try create table method from within Access to create the table in you access project.
For export to Excel we do not create Excel file. We open Excel application and copy directly data from recordset to Excel. Excel active sheet object have such method as Excel.ActiveSheet.CopyFromRecordset
For export to Excel I use such approach (Vb code):
'RecV is my generated SQl query ADODB.recordset
Dim xl As Excel.Application
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook
Set xl = New Excel.Application
Set wb = xl.Workbooks.Add
Set ws = wb.ActiveSheet
With ws.Range("A2") 'Starting from second row I export to Excel "recV" recordset
.CopyFromRecordset recV 'Here All data from recordset are copied to EXCEL
xl.Visible = True
In such way I directly copy recordset data to new excel file
For same recordset export to Access I tried such solution:
1) I create Access database, using DAO
2) I create table in Access database (*.mdb) using database.tabledefs collection
3) In created table I create fields from recV.fields collection
Here is one problem, that recV is ADO.recordset and for Access I use DAO. So problem is converting field types from ADO.field types to DAO.field types.
4) I copy record by record from ADO recordset to DAO recordset. And this operation is quite fast. At the beginnig I have doubt that exactly this operation will be very time-consuming. But performance is acceptable for us.
In such way I resolve my problem. So, if anyone else have such problems - try my approach.