Export tables to another database

X

xanders

Do somebody have some code (syntaxis) to copy (export)
tables from database A to database B.
At the moment I control it manualy.
I do not mean to link the table!
 
A

Arvin Meyer

xanders said:
Do somebody have some code (syntaxis) to copy (export)
tables from database A to database B.
At the moment I control it manualy.
I do not mean to link the table!

Here's some code that will create the second database, then fill it with the
tables and data:

Public Function CreateWebDatabase()
On Error Resume Next
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim strPath As String
strPath = "C:\FolderName\WebDB.mdb"
' Get default Workspace.
Set ws = DBEngine.Workspaces(0)
' Make sure there isn't already a file with the name of
' the new database.
If Dir(strPath) <> "" Then Kill strPath
' Create a new database
Set db = ws.CreateDatabase(strPath, dbLangGeneral)
db.Close
End Function

Public Function WebDB()
On Error Resume Next
Call CreateWebDatabase
Dim Response As Integer
Dim strPath As String
Dim strSQL As String
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim fInTrans As Boolean
'On Error GoTo Err_RollbackDB
fInTrans = False
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
strPath = "C:\FolderName\WebDB.mdb"


BeginTrans
On Error GoTo Err_RollbackDB
fInTrans = True
strSQL = "SELECT tblCountry.* INTO tblCountry IN '" & strPath & "' FROM
tblCountry"
db.Execute strSQL

strSQL = "SELECT tblSubdivision.* INTO tblSubdivision IN '" & strPath &
"' FROM tblSubdivision"
db.Execute strSQL

strSQL = "SELECT tblLot.* INTO tblLot IN '" & strPath & "' FROM tblLot"
db.Execute strSQL

ws.CommitTrans ' Commit changes

RollbackDB_Exit:
DoCmd.Hourglass False
Set db = Nothing
Set ws = Nothing
Exit Function

Err_RollbackDB:
' MsgBox "There has been an error " & strPath & " was not created",
vbCritical, "ERROR"
If fInTrans Then
ws.Rollback
End If
Resume RollbackDB_Exit

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
G

Guest

Hi Arvin

The syntaxis it isn't what I looking for because it's
create a new database and copy rows from a table instead
copy the table incl the records. So I'm not searching for
code to export data but code that copies the table and
it's contents.
I think you can help me with that becuase the code misses
some statement that will resolve my problem.
 
A

Arvin Meyer

The function WebDB() Copies 3 tables from the current database to the new
one within a transaction (if anything fails, it all fails). If you already
have the 2nd database built, you will not need the CreateWebDatabase()
function. All you need to do to make it work then is to supply the path for
the strPath variable.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
G

Guest

Hi Arvin

I just created the table in a database (copy), but the
databases where the tables are pasted has een password!
I get a error generated when I try to activate this
syntaxis.
Do you see how I can errorless activate this function?
Question nr 2, are you Dutch? (because the name)

"
Private Sub Knop0_Click()

CopyTable

End Sub

Public Function CopyTable()
On Error Resume Next
Dim Response As Integer
Dim strPath As String
Dim strSQL As String
Dim db As dao.Database
Dim ws As dao.Workspace
Dim fInTrans As Boolean
'On Error GoTo Err_RollbackDB
fInTrans = False
Set ws = DBEngine.Workspaces(0)
strPath = "C:\Temp\Test.mdb"
Set db = ws.OpenDatabase(strPath, False, False, "aaa")

BeginTrans
On Error GoTo Err_RollbackDB
fInTrans = True
strSQL = "SELECT * INTO tbl1 IN '" & strPath & "' FROM
tbl1"
db.Execute strSQL
ws.CommitTrans ' Commit changes

RollbackDB_Exit:
DoCmd.Hourglass False
Set db = Nothing
Set ws = Nothing
Exit Function

Err_RollbackDB:
MsgBox "Errornumber: " & err.Number & ", and
description: " & err.Description, vbCritical, "ERROR"
If fInTrans Then
ws.Rollback
End If
Resume RollbackDB_Exit

End Function"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top