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?