copy cells from a column in ms-access by record number

[douvakhin]

I'm working in marketing research, where i search for keywords via google adwords application, the office has provided me with an MsAccess file that contains over 2 million records , as i'm amature in Ms Access i copy 2500 record each time "because that is the limit of the search engine" by highlighting the records and then press Ctrl+C and this is awfully time consuming.
Is there any way that i can copy the records by the record number ? for example to tell Ms Access to copy from record 1 to 2500 to the clipboard so i can paste directly to the keyword application i use ? i'd very much appreciate any opinion or a solution that might help. Thanks in advance

[Mihail]

Hello !
An idea can be to use a select query.
In the criteria row write: "Between(StartRow,EndRow).
After you run the query you can select all rows at once, then Copy-Paste.

I am almost sure that exist a easier solution.
I'll looking for it.

Good luck !

[sierra7]

Hi
Access does not let users see it's internal record numbering, so that can not be used. There are a number of alternative attacks to this problem.

A) You can set up an SQL query to "SELECT TOP 2500 fldName FROM tblSourceDataCopy;". You could then Ctrl+A to select All, then Ctrl+X to cut these then Paste to your other app. Re-running the query would then select the next 2500. Clearly you would have to work on a copy of your data if you wanted to preserve the original list.

B)You could add an auto-number column to your data which could then be used as a row-number. As Mihail suggested you could then use a 'Between' statement to select any range.

C) Alternatively, you could add a new column (call it say TagID)then loop through your 2 million record putting '1' in TagID for the first 2500 and '2' for the second 2500 etc. You could then create a Select statement that filtered for any TagID and would return 2500 records.

I think there is now a function (maybe a DoCmd method)(someone on this site will have code for this) that will copy to clip-board so a certain amount can be automated but seemingly you will still have to Paste manually into your next app. If I knew how you get your data into the Google AdWords app I might be able to suggest a more automated system, but for the moment Option (A) should save a lot of scrolling.

S7

[douvakhin]

I appreciate the time and effort for answering my question , i really can't begin to think how to say thank you ,the problem is i've never dealt with ms access or any kind of database before , some terms seem obscure to me , i wouldn't ask for it unless i needed becasue right now there are not as many jobs as used to be , could you please add a step by step solution ,if it's not very much to ask , if it is , then i thank you already and have a very nice day

[sierra7]

Hi
Where to start? Writing Step-By-Step instructions is very time consuming and the intial steps depend on the version you are using.
I will presume you will start by trying option (A) because it is the most straightforward.

I suggest you start by Googling YouTube for 'Create Query in Access' for the version of Access that you are using. There are many video tutorials out there, which are much better than what I could write.

You need to create a query on your table so that it lists the field containing the AdWord. Save the query and give it a name. Anything, 'qryMyQ' would be OK.

When it is saved, reopen it in Design Mode and View as SQL, which will show it as a 'text command'. You will then have to insert the words 'TOP 2500' as shown in my first post and then re-save the query. When you next run qryMyQ it should only show 2500 records, not the full 2 million.

Make sure you are working on a copy of the database file; either a copy of the .mdb (or .accdb if 2007/2010) or a copy of the table within the database.

You should then be able to use cut and paste as describe in my first post

S7

[douvakhin]

Thank you sierra7 for taking some of your time to explain this to me, this means alot and i appreciate it , stay safe bro

[NeoPa]

Another approach to setting the TOP parameter to the SELECT clause in your query is to keep the query in design view but open the Properties pane (Use Alt-Enter or click on View | Properties). From here set the Top Values property to 2500 (or whatever).

[sierra7]

Another approach I am reminded of (having followed some discussion on DAO vs ADO) would have been to create a recordset and used .AbsolutePosition

But I can see no advantage in doing this and it was probably outside the skillset of the OP.
S7