Thanks a lot Peter. 

I like this method better than exporting the table objects themselves
because we will eventually port to SQL.

Kind regards
Mark Warner

-----Original Message-----
From: Peter N. Kipe [mailto:pkipe@c...]
Sent: 19 September 2002 09:04
To: professional vb
Subject: [pro_vb] RE: Exporting Access


Try this:

1. Start a New Standard.Exe Project.
2. Add a DataGrid (DataGrid1) to Form1.
3. Add a reference to ADOX (VB Menu -> Project -> References -> "Microsoft
ADO Ext.2.1 for DDL and Security").
4. Add a reference to ADO (VB Menu -> Project -> References -> "Microsoft
ActiveX Data Objects 2.x Library").
5. Copy/Paste the following into the Form1 code window.
6. CHANGE the m_strDBSource variable to fit YOUR environment.
7. CHANGE the m_strDBTarget variable to fit YOUR environment.
8. CHANGE the m_strTable variable to a Table Name of your choosing
(Optional).
9. Press F5 to Run.
   The program will create a DataBase.
   Copy a Table into it.
   Display the contents of the New Table in a DataGrid

Code:
Option Explicit

Private m_cn As ADODB.Connection
Private m_rs As ADODB.Recordset
Private m_strDBSource As String
Private m_strDBTarget As String
Private m_strTable As String

Private Sub Form_Load()

' Initialize
   m_strDBSource = "c:\my programs\testdata\nwind.MDB" ' <- CHANGE
   m_strDBTarget = "c:\tempwork\Test.MDB" ' <- CHANGE
   m_strTable = "Employees" ' <- CHANGE (Optional)

' Initialize Variables
   Set m_cn = New ADODB.Connection
   m_cn.CursorLocation = adUseClient
   Set m_rs = New ADODB.Recordset

' Size Screen
   With Screen
      Me.Caption = m_strTable
      Me.Move 0.1 * .Width, 0.1 * .Height, 0.8 * .Width, 0.8 * .Height
   End With

' Load / Display Data
   Call xDatabaseCopy(m_strDBSource, m_strDBTarget)
   With DataGrid1
      Set .DataSource = m_rs
   End With

End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
' Housekeeping
   Set DataGrid1.DataSource = Nothing
   If m_rs.State <> adStateClosed Then m_rs.Close
   Set m_rs = Nothing
   If m_cn.State <> adStateClosed Then m_cn.Close
   Set m_cn = Nothing
End Sub

Private Sub Form_Resize()
' Size Datagrid
   If Me.WindowState = vbMinimized Then Exit Sub
   With Me
      DataGrid1.Move 0, 0, .ScaleWidth, .ScaleHeight
   End With
End Sub

Private Sub xDatabaseCopy(ByVal i_strDBSource As String, ByVal i_strDBTarget
As String)
' Initialize
   Dim strConnect As String
   Dim strSQL As String
'
' Create Empty Target Database
'
   If Dir(i_strDBTarget) <> "" Then Kill i_strDBTarget
   strConnect = _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & i_strDBTarget & ";"
   Dim catADOX As ADOX.Catalog: Set catADOX = New ADOX.Catalog
   catADOX.Create (strConnect)
   Set catADOX = Nothing
'
' Copy Source Database Table Into Target
'
   strConnect = _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & i_strDBSource & ";"
   With m_cn
      .Open (strConnect)
   ' Make Table Query
      strSQL = _
         "SELECT * " & _
         " INTO [" & m_strTable & "] " & _
         " IN '" & m_strDBTarget & "' " & _
         " FROM [" & m_strTable & "] "
   ' Process SQL
      .Execute (strSQL)
   ' Close Connection
      .Close
   End With
'
' Retreive NEW Target Database Table Data
'
   strConnect = _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & i_strDBTarget & ";"
   With m_cn
      .Open (strConnect)
      strSQL = _
         "SELECT * " & _
         " FROM [" & m_strTable & "] "
   ' Process SQL
      Set m_rs = m_cn.Execute(strSQL)
   ' Disconnect Recordset
      Set m_rs.ActiveConnection = Nothing
   ' Close Connection
      .Close
   End With
'
End Sub


This is the key part of the code to do the From Database To Database Table
copy. To use SQL Server as the source, just change the connection string
appropriately.

Code:
'
' Copy Source Database Table Into Target
'
   strConnect = _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & i_strDBSource & ";"
   With m_cn
      .Open (strConnect)
   ' Make Table Query
      strSQL = _
         "SELECT * " & _
         " INTO [" & m_strTable & "] " & _
         " IN '" & m_strDBTarget & "' " & _
         " FROM [" & m_strTable & "] "
   ' Process SQL
      .Execute (strSQL)
   ' Close Connection
      .Close
   End With

Pete

-----Original Message-----
From: Mark Warner [mailto:Mark@S...]
Sent: Thursday, September 19, 2002 2:51 PM
To: professional vb
Subject: [pro_vb] Exporting Access


Hi All

How does one export tables from Access DB to another, programmatically. I
have found some documentation on the DoCmd object, but I have been unable to
use it.

Can anyone point me in the right direction here?

Kind regards
Mark Warner





---
Visual C# - A Guide for VB6 Developers
This book will make it easy to transfer your skills
from Visual Basic 6 to C#, the language of choice
of the .NET Framework.
http://www.wrox.com/ACON11.asp?ISBN=1861007175&p2p0059




---
Visual C# - A Guide for VB6 Developers
This book will make it easy to transfer your skills 
from Visual Basic 6 to C#, the language of choice 
of the .NET Framework.
http://www.wrox.com/ACON11.asp?ISBN=1861007175&p2p0059