3 Replies - 937 Views - Last Post: 27 July 2013 - 03:31 AM Rate Topic: -----

#1 eshajoshi  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 3
  • Joined: 25-July 13

input entry on a vb form and exporting data to a ms access 03 database

Posted 25 July 2013 - 10:44 AM

Hi,

I have been having issues with exporting data to a Micrsoft Acess 2003 database. Currently the error I have is
"Operation is not allowed when the object is closed."

I appreciate any help!


    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Pass the connection string as the first argument to the Open method
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= \\Hmstore\hmstore\Users\Esha\QST_Job_Request\QSTJOBS.mdb"
    
    cn.Open
    
    ' With recordset
    With rs
        .AddNew                        ' Adding new record and updating all the fields
        .Fields("RequestorName") = Text3.Text
        .Fields("Phone") = Text4.Text
        .Fields("TestPurpose") = Text7.Text
        .Fields("DateRequested") = Text5.Text
        .Fields("Priority") = Combo3.Text
        .Fields("DateNeeded") = Text6.Text
        .Fields("DateCompleted") = Text8.Text
        .Fields("CompletedBy") = Text10.Text
        .Fields("Program") = Text2(1).Text
        .Fields("HeadManufacturer") = Combo1(1).Text
        .Fields("PreampVendor") = Combo1(2).Text
        .Fields("PreampGeneration") = Combo1(3).Text
        .Fields("Platform") = Combo1(4).Text
        .Fields("HeadsInstalled") = Combo1(5).Text
        .Fields("Quantity") = Text2(0).Text
        .Fields("IniTest") = Combo2(0).Text
        .Fields("IniTemp") = Combo2(1).Text
        .Fields("HeadInit_A") = Combo4(0).Text
        .Fields("HeadInit_B") = Combo4(1).Text
        .Fields("HeadInit_C") = Combo4(2).Text
        .Fields("ReTest") = Combo2(2).Text
        .Fields("ReTemp") = Combo2(3).Text
        .Fields("SpecialRequest") = Text1.Text
        .Update
    End With
    
    ' Close the database.
    cn.Close
    Set cn = Nothing



Is This A Good Question/Topic? 1
  • +

Replies To: input entry on a vb form and exporting data to a ms access 03 database

#2 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: input entry on a vb form and exporting data to a ms access 03 database

Posted 26 July 2013 - 10:38 PM

i don't know much about VB6 but where is your insert query statement ?
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3517
  • View blog
  • Posts: 12,008
  • Joined: 12-December 12

Re: input entry on a vb form and exporting data to a ms access 03 database

Posted 27 July 2013 - 03:22 AM

A good first post, but you should also tell us which line the error takes you to.

You have a new Recordset, but it isn't Open. If you re-read the code you'll notice that you have a Connection, but it isn't associated with the Recordset in any way. Also notice that you have specified the database, but the Table isn't mentioned anywhere. So,

    cn.Open
    rs.Open "SELECT * FROM tblTableName", cn, adOpenDynamic    'or..
    rs.Open "tblTableName", cn, adOpenDynamic

  • This opens the recordset (you can think of it as creating the recordset),
  • tells it what data fills the Recordset,
  • what Connection it uses to retrieve this data, and
  • what CursorType it retrieves; that is, essentially, what you can do with the data

You may need a different CursorType (adOpenKeyset) I haven't checked, and sometimes you need to specify the LockType. See the docs here:

ADO Open Method

You should also Close the Recordset as well as the connection, in that order:

rs.Close
cn.Close

This post has been edited by andrewsw: 27 July 2013 - 03:35 AM

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3517
  • View blog
  • Posts: 12,008
  • Joined: 12-December 12

Re: input entry on a vb form and exporting data to a ms access 03 database

Posted 27 July 2013 - 03:31 AM

View Postvks.gautam1, on 27 July 2013 - 05:38 AM, said:

i don't know much about VB6 but where is your insert query statement ?

The OP is using the AddNew method of a Recordset to add (insert) new records.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1