newbie needs examples of creating a recordset and inserting data using 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
0
r
5/31/2010 2:28:27 AM
access 16762 articles. 3 followers. Follow

7 Replies
904 Views

Similar Articles

[PageSpeed] 7

Did you turn on the DAO reference?

open the VBA editor.

click tools - References and select Microsoft ActiveX Data Objects

i also select Microsoft DAO 

hope that helps



Chris

Just another Army Soldier


-- 
ArmySoldier72
------------------------------------------------------------------------
ArmySoldier72's Profile: http://www.thecodecage.com/forumz/member.php?u=1954
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=206202

http://www.thecodecage.com/forumz

0
ArmySoldier72
5/31/2010 6:57:22 PM
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/library/aa231060%28VS.60%29.aspx
but could not solve it.

Any advice would be appreciated.

Thanks for your reply.

0
r
5/31/2010 11:05:38 PM
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/library/aa231060%28VS.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"


0
r
5/31/2010 11:13:21 PM
On May 30, 8:28=A0pm, r <r...@r.com> 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 =3D CurrentProject.Connection
> Dim myRecordSet As New ADODB.Recordset
> myRecordSet.ActiveConnection =3D cnn1
>
> myRecordSet.Open "[Test]"
>
> Dim myRecordSet As New ADODB.Recordset
> myRecordSet.ActiveConnection =3D cnn1
>
> myRecordSet.Open =93SELECT * FROM mytest=94
>
> 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
0
Keven
6/1/2010 3:19:20 AM
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" <r@r.com> wrote in message news:hu1fqb$q0f$1@speranza.aioe.org...
> 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/library/aa231060%28VS.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"
>
> 


0
Douglas
6/1/2010 10:20:18 AM
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.databases.ms-access/browse_thread/thread/867cec635e5cc78a# 

0
r
6/1/2010 1:14:24 PM
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.databases.ms-access/browse_thread/thread/867cec635e5cc78a#
>

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.databases.ms-access/browse_thread/thread/867cec635e5cc78a# 
  is still present.
0
r
6/1/2010 1:17:11 PM
Reply:

Similar Artilces:

Outlook needs Outlook Express to work
I've installed my Outlook 2002 on my new Vista PC (I had Xp before). Now when I start Outlook it says "requires Outlook Express 4.01 or greater". I've been looking for Outlook Express to download and install but no joy. No CD came with my new Vista PC. Any ideas, short of buying a more modern Outlook? Vista doesn't actually support Outlook 2002 or older. Outlook Express is part of Internet Explorer but with the advent of Vista, it was switched to Windows Mail. There is no more Outlook Express. You could attempt however to create a profile using workgroup or c...

MS Money 95 data files
I hope that some one can answer this for me. I have used MS Money 95 for years, and it works just fine for me on Windows XP, however, I now have to reformat my hard drive, and have discovered that I can nolonger find my original install disk. Will the latest versions of Money still read the MS Money 95 data files. All that I have ever used the program for is to track my investments, and am unlikely to do any different in the future. Thanks Stan B In microsoft.public.money, Stan Banner wrote: >I hope that some one can answer this for me. >I have used MS Money 95 for years, and...

Inserting form values into a table
We have a form with values taken from an sql query that comes from two different tables. We would like to enter the information into a third table. Can some one direct me to code that will do the following: 1. Provide the Insert sql that shows us how to add the form values to the table 2. Show us how to loop while inserting the information into the table (there could be several lines on the form, each must be inserted one at a time). I have worked with Access before and have never had a problem inserting information. However, I cannot quite figure out how to insert informtion through an ...

Rollup 2 and Office 2007
I already have Roll Up 1 and the Office 2007 compatibility patch installed. If I now install roll up 2 will I have to reinstall the office 2007 compatibility? yes -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "LLoyd" wrote: > I already have Roll Up 1 and the Office 2007 compatibility patch installed. > If I now install roll up 2 will I have to reinstall the office 2007 > compatibility? ...

Using expression builder object
Hi, I'm developing a wizzard in Access which builds import templates for various data sources to a fixed set of tables. In step 3 the users must be able to build an expression ; for instance Left([Fieldx],20) . Now I would like to have a command button on my form which calls the Access expression builder to allow the users to use this to build the expression. This expression will then be stored in a text box linked to the templates table. Anybody know how to call and use this object from VBA code? -- Kind regards Noëlla DoCmd.RunCommand acCmdInvokeBuilder I th...

Excel not Access
I have designed an Access database that holds records relating to my stores audit results going back for about 5 years plus a load more information relating to these stores. This was used to produe a pack once a month, however a change in senior management means that I have got to shelve this and prodce a similar pack in Excel. The idea would be that the user could select a month or a 12 mnth date range that would produce data that could then be used to populate a number of excel templates that have been designed. Having not used excel for years I would be grateful for any suggestion...

Excel 2007
When I select cells to copy as a picture in Excel 2007, the resolution is terrible. Text and objects with shadow's are very blotchy when pasting the picture. How do you change the resolution of a 'Copy Picture'? ...

Create a distribution list from addresses in the 'To' list
Hi all, I belong to a social group, and our co-ordinator sends out an email from time to time with all the latest addresses in the To list. I already have a distribution list for the group, which then needs to be checked through and updated. Is there a simple way of transferring those addresses into my own distribution list? I'm using Outlook 2002. Everybody's fiddling around with bits of paper - I'll be a hero if I can make it easy for them! Thanks. Astley http://www.outlook-tips.net/archives/2004/20041105.htm -- Russ Valentine [MVP-Outlook] "Astley" <ast@...

Using part of a cell in a chart title
I have a chart which should get a title. However, this should be partly be used from a cell e.g. "counted with 5%" 5% should be taken from the cell and used in the title. Is this possible? Hi, Yes it's possible but all of the chart title needs to be in the cell. So you may need to use a helper cell and concatenate text and value. http://www.andypope.info/tips/tip001.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Nicole" <Nicole@discussions.microsoft.com> wrote in message news:5CB7A971-AA7F-4C34-BB42-7DC283AA2958@micro...

Input Excel 'Password to Open' through control in access form
Hi All, We know,Excel has prompt password to open it files. Is it possible to create a code that can supplies the excel prompt password?.So that when we open the excel file through our access control in a form, the excel files can be opened automatically.But when the excel files opened from its default icon,it will prompt a password first. ...

Transferring over outlook data to new XP machine
How do I transfer over my old emails, address book to my new XP machine? I have looked over the internet and found nothing the tells me EXACTLY how to do this, any help would be greatly appreciated. senior_tech@yahoo.com If your using MS Outlook copy your .PST file across and import it into the new install. >If your using MS Outlook copy your .PST file across and import it into the new install. No, don't import it. Simply use "File">"Open" -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the nam...

data input in text box
We have a form which the operator enters data in a text box. Currently we have a 'done' button on the form that the operator clicks to send the text box info to a vba program. How can we send the text box info to the vba program when the operator hits the enter key @ the end of the data entry for the text box? TIA -- _______________________________ In Christ's matchless name ted & colleen n6trf kc6rue Use the control's AfterUpdate event. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ted" <n6trf@arr...

Parsing data from one spreadsheet into another format
The data that we dump out of one machine comes in like below. %AT_1300 Bottoms|Conductivity| (Water Out) InputRange VDC1to5 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Low 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Hi 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MinScale 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MaxScale 20.0 %AT_1300 Bottoms|Conductivity| (Water Out) EngUnits mhos %AT_1300 Bottoms|Conductivity| (Water Out) StepResponseTime 1.0 %AT_1300 Bottoms|Conductivity| (Water Out) DigFiltTimeCnst 0.016 And I need to convert this data to this f...

Filter recordset using query results
Hi all I have a form based on a query called [qry Quarterly Planning], it lists all Itineraries on the system. On this form you can filter records by specifying a Start and End Date for the [ReviewDate] and/or [Specialist]. It is a subform on a main unbound form, lets call this Subform1. Along side this I have another subform (Subform2) which displays ReviewDates that exist against an Itinerary. In other words Subform1 has a start date of an activity and if the activity lasts longer than 1 day, then the additional dates are stored in Subform2 (ItineraryDates). Currently when I...

How do I use traffic lights in excel
I am wanting to use traffic lights in excel that change colour based on the result of a variance cell, ie if the result of the cell is 10 make the traffic light green, if it is 20 make the traffic light amber, if the result is 30 make the traffic light red. How do I do this? Shorty Format>Conditional Formatting>Cell Value is: Note: you can add up to 3 conditions(4 if you count default) Gord Dibben Excel MVP On Wed, 22 Dec 2004 16:35:03 -0800, Shorty <Shorty@discussions.microsoft.com> wrote: >I am wanting to use traffic lights in excel that change colour based on the &g...

Grammar check not working
Hello, I am using Word 2007 and have a problem with grammar and punctuation errors. I deliberately put two spaces between words, do not put space after a comma, write long sentences and finish a sentence without a verb but the green underline never appears. The spell check is functioning properly, no problem with that. In Word Options > Proofing, "Mark grammar errors as you type" is selected. I changed that selection and tried again but it still did not work. I used different languages as default language but no change. I would be grateful if someone could come...

Need help with Auto Filter
I have a spreedsheet that is filtered in multiple columns. I am running a "Subtotal" function to count certian rows when I filter the column. My question is this. Is there a way to save or freeze the data that the subtotal function counts in a different cell. In other words I want subtotal to count everything in a particular column but I want to be able to save that number somewhere so that when I filter again with another variable I am able to still view the first subtotal to compare the two. I hope this makes sense and thanks in advance for any assistance you can provi...

adding name /creating field/query?
Hello, I can create an invoice_number field in a query using the primary field ID from the main table as invoice_number: ID but if ID say is 100, I cannot work out how to create renewal_invoice_100 Cheers Geoff Geoff We aren't there. We can't see what you're looking at. Where did "renewal_invoice_100" come from and what does it mean? Please post the SQL statement of the query you are trying to use. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://micro...

Creating new attributes
When using Schema Manager to create new attributes, I'm not able to create a new attribute with "Lookup" as type, "Lookup" is not availabe as one of the type options. But yet some of the exist fields, for example, in Account table came with "Lookup" as type. Why? Is there a work-around? Please help. Thanks. You cannot add custom lookup's to the current release of MSCRM. The only way around is to build a custom .aspx page and build your own. Might be possible in the next release. "HEC" wrote: > When using Schema Manager to crea...

Strange Access Denied Problem with Windows 7
I got a new computer about six months ago that came with Windows Vista Home Premium 64bit. Before that I had done all of my .NET development either on an XP Pro VM or my former XP Pro computer at home. Shortly after getting my new computer at home, I also got a license for VMWare to be able to test my software on multiple platforms and configurations. I had wrote an application originally in VB.NET that was a simple backup utility. It supports mutiple backup configurations. Any given copnfiguration would define a backup which would be a list of files to backup, a list of folders to ...

Sorting Data #5
Is there formula or anyway to be able sort the below data into a format that I could create a pivot table on? I spend to many hours doing this every month. Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC Due Date: 1/16/1950 Total Payable: $100.00 Reference: Freight: Account #: 1234 Description: Name Reference 1 Amount: $100.00 Account #: 4321 Description: Name Reference 2 Amount: $100.00 Account #: 9876 Description: Name Reference 3 Amount: $100.00 Any help would be much appreciated!! You need to show a Before and After version. You still might not get any help, but your ...

How do I create several NEW templates from start to finish please?
Hi knowledgeable people. I am looking for instructions on how to create a range of basic templates that I can use, perhaps even by selecting them as 'stationery', for example; if I go to >New >Format >Apply stationery, or any other similar method. I can create new html stationery backgrounds, but this is NOT what I am looking for, as a lot of people still read email in plain text only...does this make sense? Plain text templates would be fine. I simply can't find any info on creating email templates anywhere to suit this Windows Live Mail Desktop versio...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...

HELP! Need to export hourly sales data on POS (NOT RMS)
How can I export hourly sales data across a date range? For instance, I want to show hourly sales for the month of October so I can graph it and post it in our break room. If I can't export hourly data, can I export daily sales? The built-in reports don't address this data format. This is a multi-part message in MIME format. ------=_NextPart_000_008E_01C826DC.CBC512D0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit Mark, This should work for you. Keep in mind it takes up to 5-10 minutes to load...

Radar chart in Access 2007 report
Can you add a Radar chart to an access 2207 report? ...