VB and MS Access

[mike]

Does anyone have an idea on how I can transfer records from a SQL table to
a MS access table using a query result? I am programming this in vb and
therefore the Data Transformation Service GUI is not viable for me.

Thanks.

Mike

[DeGojs]

Connect to the both SQL Server and MS Access database.
Query the SQL Server and get a recordset.
Update the Acess db with the retreived data.

Is this what you've been looking for?
-------------------------------------
Code:

Dim rsSQL As New ADODB.Recordset ' recordset that will hold the data from
SQL server database
Dim rsMDB As New ADODB.Recordest ' recordset that will be used to update the
Access database
' sure, you also NEED two ADODB.Connections (let's assume you know how to do
this)
'

'
' connect to the SQL Server and perform the query to fill the recordset
rsSQL with the data
' to connect to the MS Access database you'll use connection string such as
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/database2.mdb"
' now let's update Access database with the retreived data:
rsSQL.MoveFirst
Do Until rsSQL.EOF
rsMDB.AddNew
rsMDB("field1_name")=rsSQL("field1_name")
rsMDB("field2_name")=rsSQL("field2_name")
' etc - add more code here if necessary, like
rsMDB("fielnn_name")=rsSQL("fieldn_name") okay?
rsMDB.Update
rsSQL.MoveNext
Loop

etc

If you need some more help, post again




"mike" <mike@geomedia.ca> wrote in message news:3a88547e$1@news.devx.com...
>
> Does anyone have an idea on how I can transfer records from a SQL table to
> a MS access table using a query result? I am programming this in vb and
> therefore the Data Transformation Service GUI is not viable for me.
>
> Thanks.
>
> Mike
>
>

[Robert Gelb]

I'd suggest a really cool Rs2Anything (or something like that) at

http://www.smithvoice.com/vbfun.htm

--
Robert Gelb
www.vbRad.com
Source, Tips, Tricks, Components

"DeGojs" <degojs@hotmail.com> wrote in message
news:3a889aa2$1@news.devx.com...
> Connect to the both SQL Server and MS Access database.
> Query the SQL Server and get a recordset.
> Update the Acess db with the retreived data.
>
> Is this what you've been looking for?
> -------------------------------------
> Code:
>
> Dim rsSQL As New ADODB.Recordset ' recordset that will hold the data
from
> SQL server database
> Dim rsMDB As New ADODB.Recordest ' recordset that will be used to update
the
> Access database
> ' sure, you also NEED two ADODB.Connections (let's assume you know how to
do
> this)
> '
>
> '
> ' connect to the SQL Server and perform the query to fill the recordset
> rsSQL with the data
> ' to connect to the MS Access database you'll use connection string such
as
> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/database2.mdb"
> ' now let's update Access database with the retreived data:
> rsSQL.MoveFirst
> Do Until rsSQL.EOF
> rsMDB.AddNew
> rsMDB("field1_name")=rsSQL("field1_name")
> rsMDB("field2_name")=rsSQL("field2_name")
> ' etc - add more code here if necessary, like
> rsMDB("fielnn_name")=rsSQL("fieldn_name") okay?
> rsMDB.Update
> rsSQL.MoveNext
> Loop
>
> etc
>
> If you need some more help, post again
>
>
>
>
> "mike" <mike@geomedia.ca> wrote in message
news:3a88547e$1@news.devx.com...
> >
> > Does anyone have an idea on how I can transfer records from a SQL table
to
> > a MS access table using a query result? I am programming this in vb and
> > therefore the Data Transformation Service GUI is not viable for me.
> >
> > Thanks.
> >
> > Mike
> >
> >
>
>

"mike" <mike@geomedia.ca> wrote:
>
>Does anyone have an idea on how I can transfer records from a SQL table
to
>a MS access table using a query result? I am programming this in vb and
>therefore the Data Transformation Service GUI is not viable for me.
>
>Thanks.
>
>Mike
>
>
This saves it to a file can be a dat or xml file Part One

------------------------------------------------------

Set adoConnection = New ADODB.Connection



adoConnection.Open SconnectString
Set adoRsFields = adoConnection.Execute("SELECT XXX From YYY)

CommonDialog1.ShowSave
sNewFile = CommonDialog1.FileName & "." & CommonDialog1.Filter
adoRsFields.Save sNewFile, adPersistXML ' adPersistADTG

adoRsFields.Close
adoConnection.Close
Set adoRsFields = Nothing
Set adoConnection = Nothing
cmdRead.Enabled = True
Exit Sub
-------------------------------------------------------

Reads the resultset you would open another connection to target database
and file the corresponding fields.
---------------------------------------------------
Dim adoRsFields As ADODB.Recordset
Dim sCurrentTable As String
Dim sNewFile As String
'On Error GoTo errHandler

Set adoConnection = New ADODB.Connection



adoConnection.Open SconnectString
Set adoRsFields = New ADODB.Recordset

CommonDialog1.ShowOpen
sNewFile = CommonDialog1.FileName
adoRsFields.Open sNewFile, "Provider=MSPersist"

you have to add more the harder way

------------------------------------------------
Simple way is open two connections do the query on the source, open a second
connection and set the resultset field values of on the first equal to the
second (Target). Thats the 5 minute view of it. The code I gave you would
work if the databases were not on the same network and you had to transfer
data.

Let me know don't shoot me!Just some ideas