VB and MS Access


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: VB and MS Access

  1. #1
    mike Guest

    VB and MS Access


    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



  2. #2
    DeGojs Guest

    Re: VB and MS Access

    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
    >
    >




  3. #3
    Robert Gelb Guest

    Re: VB and MS Access

    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
    > >
    > >

    >
    >




  4. #4
    Guest

    Re: VB and MS Access


    "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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center