SQL 7 And Access?

Is there anybody know how to export data from SQL table or View into MS Access by VB.
I have use Import/export menu in sql enterprise but that's manual and I want to do that from vb code.

[lenin]

Hi,
you can use DTS in SQL Server to export the data as a scheduled job, by creating a "DTS Package". This consists of a schema created by you ( very easily done ), consisting of a source -> Transform -> Output. The output could be your Access Database. There really isn't any need to get VB involved.

I have had some trouble with permission issues when scheduling dts jobs.

There is a lot of documentation on this in BOL.


HTH

Lenin.

Thanks,
But I only want to do this when I do an event from VB form.

[lenin]

I think, you would need to check, but there is access to the dtsrun interface from within vb. i.e. you may be able to run a dts package as a result of an event in VB. I will also try it from here and let you know.

This may be a while though.

Lenin

[Clunietp]

I wrote this last week, all you have to do is pass you connection strings to this function (ie 1 for SQL Server and 1 for MS Access), the table name, and if you want to delete all data in the desination table first

'uses ADO 2.x

Public Sub CopyData(TableName As String, SourceConnectionString As String, DestinationConnectionString As String, DeleteExistingData As Boolean)
    
    On Error GoTo Err_Handler
    
    Dim cnSource As ADODB.Connection
    Dim cnDestination As ADODB.Connection
    Dim rsSource As ADODB.Recordset
    
    Dim strSQL As String
    Dim lngCounter As Long
    
    Set cnSource = New Connection
    Set cnDestination = New Connection
    
    'open connections
    cnSource.Open SourceConnectionString
    cnDestination.Open DestinationConnectionString
    
    'start transaction for destination in case of error
    cnDestination.BeginTrans
    
    'get source data
    Set rsSource = cnSource.Execute("Select * from [" & TableName & "]", , adCmdText)
    
    'delete data if necessary
    If DeleteExistingData = True Then
        cnDestination.Execute "Delete from [" & TableName & "]"
    End If
    
    Do Until rsSource.EOF = True
        'prepare SQL statement
        '==============================================
        strSQL = "Insert into [" & TableName & "] ("
        
        'get fields
        For lngCounter = 0 To rsSource.Fields.Count - 1
            strSQL = strSQL & "[" & rsSource.Fields(lngCounter).Name & "], "
        Next lngCounter
        
        'remove last comma & space
        strSQL = Left(strSQL, Len(strSQL) - 2)
        
        strSQL = strSQL & ") VALUES ("
        
        'get data
        For lngCounter = 0 To rsSource.Fields.Count - 1
            strSQL = strSQL & "'" & Replace(rsSource.Fields(lngCounter).Value & "", "'", "''") & "', "
        Next lngCounter
        
        'remove last comma & space
        strSQL = Left(strSQL, Len(strSQL) - 2)
        
        'add closing parenthesis
        strSQL = strSQL & ")"
        
        'update destination table
        cnDestination.Execute strSQL, , adCmdText
        
        rsSource.MoveNext
        DoEvents
    Loop
    'commit transaction
    cnDestination.CommitTrans
    Exit Sub
    
Err_Handler:
    cnDestination.RollbackTrans
    Err.Raise Err.Number, Err.Source, Err.Description
End Sub

Thanks,
But that's insert data to another SQL table or SQL database.
In here I want to insert data From SQL table into Access table.

[Clunietp]

That will work fine, assuming you already have the tables created, and you just need to get the data. Do you need to create the tables as well?