Copying Database Tables

[1mightymouse]

I want to copy database tables that reside on SQLServer to a client machine that only has Access for the user to view on the road. How do I do that?

Thanks!

[TheBao]

Open MSSQL Server -->Enterprise manager then select the table you want to export.

At the destination, select the MS Access.

The new table should be stored in a *.mdb file you specified during the export wizard.

Hope this help
The Bao

[1mightymouse]

Thanks for the info The Bao, but I should have made my question a little more clear. I'm working on a Visual Basic project that connects to a SQLServer DB. One of the things we want the user to have the ability to do is copy, or download, the database onto his desktop, or laptop, with a click of a button so they can view the information on the road, just like they could view it in the office. So that means, the application would be connecting to the local Access database instead of the server database. The user doesn't have direct access privileges to the SQLServer DB except through the application.

I hope this makes it a little more clear. If anyone has any idea onhow to do this, I'd greatly appreciate it!

Thanks!

[sebs]

why can't you use filecopy

filecopy "w:\database.mdb","c:\database.mdb"

[parksie]

Because he's using SQL Server

[Clunietp]

here's some code I have that mostly works I don't especially feel like troubleshooting it now, but maybe you can put the finishing touches on it

it's got a couple bugs converting from a SQL to Access DB, but an Access to Access transfer works fine....

This code will copy a table structure to another database

'uses ADO 2.x and ADOX

'uses ADO 2.x and Ado 2.x EXT

    Dim axCatSource As ADOX.Catalog
    Dim axCatDestination As ADOX.Catalog
    
    Dim tblSource As ADOX.Table
    Dim tblDestination As ADOX.Table
    
    Dim i As Integer
    
    Dim cnSource As ADODB.Connection
    Dim cnDestination As ADODB.Connection
    
    Dim strSourceConnectString As String
    Dim strDestinationConnectString As String

    Set cnSource = New Connection
    Set cnDestination = New Connection
    
    strSourceConnectString = "Provider=SQLOLEDB;Data Source=SRV;Integrated Security=SSPI;Database=pubs"
    
    strDestinationConnectString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=c:\NewDB.mdb"
    
    cnSource.Open strSourceConnectString

    Set axCatSource = New Catalog
    Set axCatDestination = New Catalog
        
    axCatSource.ActiveConnection = cnSource
    axCatDestination.Create strDestinationConnectString
    
    Set tblDestination = New Table
    Set tblSource = axCatSource.Tables("Authors")
    
    For i = 0 To tblSource.Columns.Count - 1
        tblDestination.Columns.Append tblSource.Columns(i).Name, tblSource.Columns(i).Type, tblSource.Columns(i).DefinedSize
                
    Next i
    
    tblDestination.Name = tblSource.Name
    axCatDestination.Tables.Append tblDestination

and this code will copy the data

'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