Home > Archive > Visual Basic > April 2006 > Recordset to File (MDB or Excel)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Recordset to File (MDB or Excel)
|
|
|
| Is it possible to save the result of a recordset to an Access MDB or Excel
file from within VB? If possible, how do I do it?
Thanks
Emma
| |
|
| Yes.
What parts are you having issues with? Meaning, you seem to know how to
create a recordset and populate it with data since you want to save it
elsewhere. Are you having issues with the connection to Access or Excel or
problems with generating the SQL statement?
--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
Veign's Blog
http://www.veign.com/blog
--
"Emma" <Emma@discussions.microsoft.com> wrote in message
news:BC0559E8-3EB1-4A4E-AEE3-FFF1C77A4FA9@microsoft.com...
> Is it possible to save the result of a recordset to an Access MDB or Excel
> file from within VB? If possible, how do I do it?
>
> Thanks
> Emma
>
| |
|
|
The data is coming from a SQL database and I want to save the recordset to
an Excel file or an MDB file so that the users can work with the recordset
locally on their computer. The users also want to use the result in a mail
merge using Word.
"Veign" wrote:
> Yes.
>
> What parts are you having issues with? Meaning, you seem to know how to
> create a recordset and populate it with data since you want to save it
> elsewhere. Are you having issues with the connection to Access or Excel or
> problems with generating the SQL statement?
>
> --
> Chris Hanscom - Microsoft MVP (VB)
> Veign's Resource Center
> http://www.veign.com/vrc_main.asp
> Veign's Blog
> http://www.veign.com/blog
> --
>
>
> "Emma" <Emma@discussions.microsoft.com> wrote in message
> news:BC0559E8-3EB1-4A4E-AEE3-FFF1C77A4FA9@microsoft.com...
>
>
>
| |
| Schmidt 2006-04-25, 6:57 pm |
|
"Emma" <Emma@discussions.microsoft.com> schrieb im Newsbeitrag
news:BC0559E8-3EB1-4A4E-AEE3-FFF1C77A4FA9@microsoft.com...
> Is it possible to save the result of a recordset to an Access MDB
> or Excel file from within VB? If possible, how do I do it?
Yes, the easiest way is per Automation (needs Excel as a prerequisite):
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
Set objWrkSht = objExcel.ActiveWorkbook.Sheets(1)
objExcel.Visible = True 'if you want to see, what's going on
'fill rs-data-container from Cell[2,2]-offset in one call
objWrkSht.Cells(2, 2).CopyFromRecordset Rs
'... the WorkSheet now only needs to be saved
'...eventually after filling some Header-Info into the first Row
'Cleanup
Set objWrkSht = Nothing
Set objExcel = Nothing
If Excel is not a given Prerequisite, then you have to deal with ADOxxx, to
create an *.mdb and an appropriate Table with the correct Fields in it.
You can also use the Excel-Isam-Driver, to write the Rs-Data into an
XL-Table/File directly - but those questions are better asked in a
"DB/ADO-Group".
Olaf
| |
|
| OK. But again, what are you having issues with.
--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
Veign's Blog
http://www.veign.com/blog
--
"Emma" <Emma@discussions.microsoft.com> wrote in message
news:C06CD66C-F543-4D8F-818C-3509B49D8342@microsoft.com...[color=darkred]
>
> The data is coming from a SQL database and I want to save the recordset to
> an Excel file or an MDB file so that the users can work with the recordset
> locally on their computer. The users also want to use the result in a mail
> merge using Word.
>
> "Veign" wrote:
>
| |
|
| I am trying to figure out how to write the result of a recordset to a MDB or
Excel file. Schmidt gave an example on writing to Excle file. I would like to
figure out how I can create a new MDB file from the recordset and save the
result in a table.
Thanks
Emma
"Veign" wrote:
> OK. But again, what are you having issues with.
>
> --
> Chris Hanscom - Microsoft MVP (VB)
> Veign's Resource Center
> http://www.veign.com/vrc_main.asp
> Veign's Blog
> http://www.veign.com/blog
> --
>
>
> "Emma" <Emma@discussions.microsoft.com> wrote in message
> news:C06CD66C-F543-4D8F-818C-3509B49D8342@microsoft.com...
>
>
>
| |
| Paul Clement 2006-04-25, 6:57 pm |
| On Tue, 25 Apr 2006 08:31:03 -0700, Emma <Emma@discussions.microsoft.com> wrote:
¤ Is it possible to save the result of a recordset to an Access MDB or Excel
¤ file from within VB? If possible, how do I do it?
¤
Do you need to save it from a Recordset or can you save it directly from SQL Server to Access or
Excel?
Paul
~~~~
Microsoft MVP (Visual Basic)
| |
|
| The query to build the recordset is generated in the VB application based on
the user’s criteria. With that in mind, I will be saving it from the
recordset, unless there is another way to save it directly from SQL Server.
What do you have in mind?
Thanks
"Paul Clement" wrote:
> On Tue, 25 Apr 2006 08:31:03 -0700, Emma <Emma@discussions.microsoft.com> wrote:
>
> ¤ Is it possible to save the result of a recordset to an Access MDB or Excel
> ¤ file from within VB? If possible, how do I do it?
> ¤
>
> Do you need to save it from a Recordset or can you save it directly from SQL Server to Access or
> Excel?
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>
| |
| Paul Clement 2006-04-25, 6:57 pm |
| On Tue, 25 Apr 2006 11:06:02 -0700, Emma <Emma@discussions.microsoft.com> wrote:
¤ The query to build the recordset is generated in the VB application based on
¤ the user’s criteria. With that in mind, I will be saving it from the
¤ recordset, unless there is another way to save it directly from SQL Server.
¤ What do you have in mind?
¤
If you want to try the direct method there a couple of examples below:
Sub ImportSQLServerToAccess()
Dim cnn As New ADODB.Connection
Dim SQLString As String
Dim rs As New ADODB.Recordset
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=e:\My Documents\db1 XP.mdb;" & _
"Jet OLEDB:Engine Type=4"
'SQLString = "SELECT * INTO Orders FROM [Orders] IN '' [ODBC;Driver={SQL
Server};Server=(local);Database=Northwin
d;Trusted_Connection=yes];"
SQLString = "INSERT INTO Orders (CustomerID, EmployeeID, ShippedDate) SELECT CustomerID, EmployeeID,
Iif(ShippedDate < Now(),Now(),ShippedDate) FROM [Orders] IN '' [ODBC;Driver={SQL
Server};Server=(local);Database=Northwin
d;Trusted_Connection=yes];"
cnn.Execute SQLString
rs.Close
cnn.Close
Set cnn = Nothing
End Sub
Sub ImportSQLServerToExcel()
Dim cnn As New ADODB.Connection
Dim strSQL As String
Dim rs As New ADODB.Recordset
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Test Files\Book20.xls;Extended Properties=Excel 8.0;"
strSQL = "INSERT INTO [Orders] SELECT * FROM [ODBC;Driver={SQL
Server};Server=(local);Database=Northwin
d;Trusted_Connection=yes].[Orders];"
cnn.Execute strSQL
cnn.Close
Set cnn = Nothing
End Sub
Paul
~~~~
Microsoft MVP (Visual Basic)
| |
| Tark Siala 2006-04-29, 6:56 pm |
| hi
by easy and fast way you can save Recordset as XML File.
or save as normal recordset file then you can load it again.
recordset.save
--
Tarek M. Siala
"Emma" <Emma@discussions.microsoft.com> wrote in message
news:BC0559E8-3EB1-4A4E-AEE3-FFF1C77A4FA9@microsoft.com...
> Is it possible to save the result of a recordset to an Access MDB or Excel
> file from within VB? If possible, how do I do it?
>
> Thanks
> Emma
>
|
|
|
|
|