A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

newbie needs examples of creating a recordset and inserting datausing it in Access 2007?



 
 
Thread Tools Display Modes
  #1  
Old May 31st, 2010, 03:28 AM posted to comp.databases.ms-access,microsoft.public.access
r
external usenet poster
 
Posts: 5
Default 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  
Old June 1st, 2010, 12:05 AM posted to microsoft.public.access
r
external usenet poster
 
Posts: 5
Default 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  
Old June 1st, 2010, 12:13 AM posted to microsoft.public.access
r
external usenet poster
 
Posts: 5
Default 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  
Old June 1st, 2010, 04:19 AM posted to comp.databases.ms-access,microsoft.public.access
Keven Denen
external usenet poster
 
Posts: 123
Default 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  
Old June 1st, 2010, 11:20 AM posted to microsoft.public.access
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default 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  
Old June 1st, 2010, 02:14 PM posted to comp.databases.ms-access,microsoft.public.access
r
external usenet poster
 
Posts: 5
Default 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  
Old June 1st, 2010, 02:17 PM posted to comp.databases.ms-access,microsoft.public.access
r
external usenet poster
 
Posts: 5
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:34 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.