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!
Printable View
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!
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
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!
why can't you use filecopy
filecopy "w:\database.mdb","c:\database.mdb"
Because he's using SQL Server :rolleyes:
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
and this code will copy the dataCode:'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
Code:'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