[RESOLVED] Data Extraction

[Daniel_Cooper]

Hi all,

Does anyone have any info on how to extract data from Access using VB and then creating an excel file containing those data? Any recommended book/sites? I'm willing to pay for any sample code/program. My email [email]

thanks

Daniel

[RobDog888]

Welcome to the Forums.

You shouldnt post your email as you will get spammed. Members can contact you via replying to this post or via email, if you specified the option in your control panel.


Moved from Classic VB.

[RobDog888]

What version of Access and Excel are you running? Do you have any code written yet?

[dglienna]

Welcome to the forums.
You should edit that post to remove your email address. The forums are scanned by bots, and your address will be open to spammers in a matter of hours. Include it in your profile, so only members can see it. Or at the very least use AT instead of the @ symbol so that the bots don't recognize it.

I have this sample that does exactly what you want. No charge!
(I wouldn't know who to split it with )

[Bruce Fox]

Hi Daniel, Welcome.

Here is an example from M$: http://support.microsoft.com/default...NoWebContent=1

There are also plenty of other examples posted on this Forum, just do a Search

[Daniel_Cooper]

i'm running access & excel 2003. i've not started anything, i need a guide to follow. i'm just following Vb from scratch. you have any suggestions?

thanks

[Bruce Fox]

As an Alternate..

Here is the Access part (need to add the Excel load routine):
Note: Change the .MDB file to suit your needs.

Option Explicit
 
'Reference: M$ ActiveX Data Objects 2.X Library
 
Private Sub Form_Load()
Dim strSQL As String
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
 
On Error GoTo Err_Handler
 
    Set rst = New ADODB.Recordset
    Set cnn = New ADODB.Connection
 
    strSQL = "SELECT * FROM [tblCustomers]"
 
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Customers.mdb;Persist Security Info=False;"
 
    rst.CursorLocation = adUseClient 'Set to enable Cursor movement
    rst.Open strSQL, cnn, adOpenForwardOnly, adLockOptimistic
 
    'Check RS for data
    If Not rst.BOF And Not rst.EOF Then
 
        MsgBox rst.GetString '<<<<< Do your Excel stuff here!!!!!!!!!!
 
    End If
 
    rst.Close
    cnn.Close
 
    Set rst = Nothing
    Set cnn = Nothing
 
Exit Sub
 
Err_Handler:
 
    If Not (rst Is Nothing) Then
        If rst.State = adStateOpen Then
            rst.Close
            Set rst = Nothing
        End If
    End If
 
    If Not (cnn Is Nothing) Then
        If cnn.State = adStateOpen Then
            cnn.Close
            Set cnn = Nothing
        End If
    End If
 
    MsgBox "Description: " & Err.Description & vbCrLf & _
        "Number: " & Err.Number, vbOKOnly + vbInformation, "Error"
End Sub

[RobDog888]

Yes, Bruce's MS link is a clean simple example. There is still another way using only Access and Excel but since you want to use VB6 too, go with the link in post #5.

[Daniel_Cooper]

Thanks a lot for all the help peeps!