I want to install a MS Access database on MS SQL Server in VB6.
Until now I managed to install database structure using T-SQL script.
Now I need to populate the DB on server with data from the original .mdb file; I need a colection of all (user)tables existing in the DB, and then use some kind of
INSERT INTO table1
  SELECT * FROM table2;
table1 being on server DB
table2 being on local DB
can u help me??

To decap_itate:

Try using the upsizing wizard. It is an excellent tool of creating an SQL Server database from an existing Access database. It works well for me.


JFe'

I hope these code segments will help.
---------------
Option Explicit

Dim rsVirtual As Recordset 'Declare a virtual recordset

Private Sub Form_Activate()
--
--
createVirtual
readTable1
transferToTable2
--
--
End Sub

Private Sub createVirtual()
'-------------------------------
' this process creates the
' virtual recordset. a virtual
' recordset is not connected to
' the database, nor is it a part
' of the database. it just serves
' as the medium of transfer.
' use the same record structure as
' that of the source and target
' tables.
'--------------------------------
Set rsVirtual = New Recordset
With rsVirtual.Fields
.Append "<recordset column/field name>",type, size
--
--
End With
rsVirtual.Open
End Sub


Private Sub readTable1()
'---------------------------------
' this process reads table 1 which
' is the ms access table which
' serves as the source of records
' to be transferred. the virual
' recordset receives the records from
' the access table.
' make sure that you open a connection
' to your access database.
'---------------------------------
Dim dbCon As New ADODB.Connection
Dim rsRec As New ADODB.Recordset
Dim strSQL As String

dbCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                         "Data Source = <Access database pathname>"
dbCon.Open
dbCon.CursorLocation = adUseServer

strsql = "SELECT * FROM <table1>"
rsRec.Open strSQL, dbCon, adOpenKeyset, adLockOptimistic

rsRec.MoveFirst
While not rsRec.EOF
With rsVirtual
      .AddNew
      .Fields(<1st column name of virtual recordset>) = _
      rsRec(<corresponding column name from the SQL table>)
      '-- continue until the last colum/field of the tables
      .Update
End With
rsRec.MoveNext
Wend
'-- now close the connection with the Access database --
set rsRec=nothing
set dbCon=nothing
End Sub


Private Sub transferToTable2()
'------------------------------------
' this process transfers the records
' from table 1 which temporarily
' reside in the virtual recordset
' you must establish a connection
' to the target SQL Server database
'-----------------------------------
Dim dbCon As New ADODB.Connection
Dim rsRec As New ADODB.Recordset
Dim strSQL As String

dbCon.ConnectionString = "<the SQL database connection string
                          database pathname>"
dbCon.Open
dbCon.CursorLocation = adUseServer

strsql = "SELECT * FROM table2 WHERE 1=2"
rsRec.Open strSQL, dbCon, adOpenKeyset, adLockOptimistic

rsVirtual.MoveFirst
While not rsVirtual.EOF
With rsRec
     .AddNew
     .Fields(<1st column name of SQL Server table>) = _
         rsVirtual _
             (<corresponding column name from the Access table>)
     '-- continue until the last colum/field of the tables
     .Update
End With
rsVirtual.MoveNext
Wend
'-- now close the connection with the SQL Server database --
set rsRec=nothing
set dbCon=nothing
End Sub


The above uses an intermediate table which I called the virtual recordset (rsVirtual).

It should make things easy for the transfer.

I hope it will be of help.

Jorge