If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
newbie needs examples of creating a recordset and inserting datausing it in Access 2007?
I have created a Access 2007 database, Test.mdb, created a table called
"mytest" and created required fields in it. Now, using VBA(Visual Basic for Applications) I want to insert data into it using a Recordset from a Word document. Can anyone please point me to a proper link where there is an example of how data is inserted using a Recordset into a Access 2007 table? I created one using resources on Web, but it is not correct. Sub TestRecordsetexample Dim cnn1 As ADODB.Connection Set cnn1 = CurrentProject.Connection Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = cnn1 myRecordSet.Open "[Test]" Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = cnn1 myRecordSet.Open “SELECT * FROM mytest” MsgBox myRecordSet.fields(0).value myRecordSet.Close cnn1.Close End Sub I get a compile time error for "Dim cnn1 As ADODB.Connection" as "User defined Type not defined" I want to choose Tools-References from the VBA Editor menu bar, but References is disabled and I cannot choose it. I guess I have to set up the connection from Word 2007(where the above code is present as a Macro) to Access 2007(where the database is) which I have not done. Any advice would be welcome on how to fix the above error and some examples of using Recordsets with Access 2007. Thanks |
#2
|
|||
|
|||
newbie needs examples of creating a recordset and inserting datausing it in Access 2007?
On 5/31/2010 2:57 PM, ArmySoldier72 wrote:
Did you turn on the DAO reference? Yes, I did it. open the VBA editor. click tools - References and select Microsoft ActiveX Data Objects i also select Microsoft DAO hope that helps The code(modified a bit) is below: Sub ReturnTableText() Dim oTable As Table Dim oRow As Row Dim oRng As Range Dim sText As String Dim count As Integer Dim dbMyDB As DAO.Database Dim myRecordSet As DAO.Recordset Set dbMyDB = OpenDatabase("C:\mydatabase.accdb") ' this line throws an ' error Run Time Error 429 ActiveX component can't create object or ' return reference to this object Set myRecordSet = dbMyDB.OpenRecordset("Table1", dbOpenDynaset) sText = "" count = 0 For Each oTable In ActiveDocument.Tables For Each oRow In oTable.Rows If oRow.Cells.count 1 Then Set oRng = oRow.Cells(oRow.Cells.count).Range oRng.End = oRng.End - 1 myRecordSet.Fields(count).Value = oRng.Text & Chr(44) MsgBox myRecordSet.Fields(count).Value count = count + 1 End If Next oRow Next oTable myRecordSet.Close dbMyDB.Close End Sub I checked the documentation at http://msdn.microsoft.com/en-us/libr...8VS.60%29.aspx but could not solve it. Any advice would be appreciated. Thanks for your reply. |
#3
|
|||
|
|||
newbie needs examples of creating a recordset and inserting datausing it in Access 2007?
On 5/31/2010 7:05 PM, r wrote:
On 5/31/2010 2:57 PM, ArmySoldier72 wrote: Did you turn on the DAO reference? Yes, I did it. open the VBA editor. click tools - References and select Microsoft ActiveX Data Objects i also select Microsoft DAO hope that helps The code(modified a bit) is below: Sub ReturnTableText() Dim oTable As Table Dim oRow As Row Dim oRng As Range Dim sText As String Dim count As Integer Dim dbMyDB As DAO.Database Dim myRecordSet As DAO.Recordset Set dbMyDB = OpenDatabase("C:\mydatabase.accdb") ' this line throws an ' error Run Time Error 429 ActiveX component can't create object or ' return reference to this object Set myRecordSet = dbMyDB.OpenRecordset("Table1", dbOpenDynaset) sText = "" count = 0 For Each oTable In ActiveDocument.Tables For Each oRow In oTable.Rows If oRow.Cells.count 1 Then Set oRng = oRow.Cells(oRow.Cells.count).Range oRng.End = oRng.End - 1 myRecordSet.Fields(count).Value = oRng.Text & Chr(44) MsgBox myRecordSet.Fields(count).Value count = count + 1 End If Next oRow Next oTable myRecordSet.Close dbMyDB.Close End Sub I checked the documentation at http://msdn.microsoft.com/en-us/libr...8VS.60%29.aspx but could not solve it. Any advice would be appreciated. Thanks for your reply. Need to clarify one part. I open Access 2007, open the mydatabase.accdb database, run the above code as a macro and it runs fine. I open a Word 2007 document, try to run this as a macro and I get the above error "Run Time Error 429 ActiveX component can't create object or return reference to this object" |
#4
|
|||
|
|||
newbie needs examples of creating a recordset and inserting datausing it in Access 2007?
On May 30, 8:28*pm, r wrote:
I have created a Access 2007 database, Test.mdb, created a table called "mytest" and created required fields in it. Now, using VBA(Visual Basic for Applications) I want to insert data into it using a Recordset from a Word document. Can anyone please point me to a proper link where there is an example of how data is inserted using a Recordset into a Access 2007 table? I created one using resources on Web, but it is not correct. Sub TestRecordsetexample Dim cnn1 As ADODB.Connection Set cnn1 = CurrentProject.Connection Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = cnn1 myRecordSet.Open "[Test]" Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = cnn1 myRecordSet.Open “SELECT * FROM mytest” MsgBox myRecordSet.fields(0).value myRecordSet.Close cnn1.Close End Sub I get a compile time error for "Dim cnn1 As ADODB.Connection" as "User defined Type not defined" I want to choose Tools-References from the VBA Editor menu bar, but References is disabled and I cannot choose it. I guess I have to set up the connection from Word 2007(where the above code is present as a Macro) to Access 2007(where the database is) which I have not done. Any advice would be welcome on how to fix the above error and some examples of using Recordsets with Access 2007. Thanks You need to add a reference to ADO before you can use it. If you insist on using ADO, you need to figure out why you can't get into your references. An alternate solution might be to think about using DAO as it is native to Access. Keven |
#5
|
|||
|
|||
newbie needs examples of creating a recordset and inserting data using it in Access 2007?
You need to ensure that the reference to DAO is set in Word.
-- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) "r" wrote in message ... On 5/31/2010 7:05 PM, r wrote: On 5/31/2010 2:57 PM, ArmySoldier72 wrote: Did you turn on the DAO reference? Yes, I did it. open the VBA editor. click tools - References and select Microsoft ActiveX Data Objects i also select Microsoft DAO hope that helps The code(modified a bit) is below: Sub ReturnTableText() Dim oTable As Table Dim oRow As Row Dim oRng As Range Dim sText As String Dim count As Integer Dim dbMyDB As DAO.Database Dim myRecordSet As DAO.Recordset Set dbMyDB = OpenDatabase("C:\mydatabase.accdb") ' this line throws an ' error Run Time Error 429 ActiveX component can't create object or ' return reference to this object Set myRecordSet = dbMyDB.OpenRecordset("Table1", dbOpenDynaset) sText = "" count = 0 For Each oTable In ActiveDocument.Tables For Each oRow In oTable.Rows If oRow.Cells.count 1 Then Set oRng = oRow.Cells(oRow.Cells.count).Range oRng.End = oRng.End - 1 myRecordSet.Fields(count).Value = oRng.Text & Chr(44) MsgBox myRecordSet.Fields(count).Value count = count + 1 End If Next oRow Next oTable myRecordSet.Close dbMyDB.Close End Sub I checked the documentation at http://msdn.microsoft.com/en-us/libr...8VS.60%29.aspx but could not solve it. Any advice would be appreciated. Thanks for your reply. Need to clarify one part. I open Access 2007, open the mydatabase.accdb database, run the above code as a macro and it runs fine. I open a Word 2007 document, try to run this as a macro and I get the above error "Run Time Error 429 ActiveX component can't create object or return reference to this object" |
#6
|
|||
|
|||
newbie needs examples of creating a recordset and inserting datausing it in Access 2007?
On 5/31/2010 11:19 PM, Keven Denen wrote:
Thanks You need to add a reference to ADO before you can use it. If you insist on using ADO, you need to figure out why you can't get into your references. An alternate solution might be to think about using DAO as it is native to Access. Keven Thanks, that got fixed, but now i face another error http://groups.google.com/group/comp....cec635e5cc78a# |
#7
|
|||
|
|||
newbie needs examples of creating a recordset and inserting datausing it in Access 2007?
On 6/1/2010 9:14 AM, r wrote:
On 5/31/2010 11:19 PM, Keven Denen wrote: Thanks You need to add a reference to ADO before you can use it. If you insist on using ADO, you need to figure out why you can't get into your references. An alternate solution might be to think about using DAO as it is native to Access. Keven Thanks, that got fixed, but now i face another error http://groups.google.com/group/comp....cec635e5cc78a# Should clarify, the error of error of "Dim cnn1 As ADODB.Connection" as User defined Type not defined" got fixed, but the error at http://groups.google.com/group/comp....cec635e5cc78a# is still present. |
Thread Tools | |
Display Modes | |
|
|