Is it possible to use ADODB.Recordset.Open in a loop

[NeoPa]

HibHib:
Sorry for extending this post past its original question

That was almost unavoidable from the original question, but we both know that your questions will improve with experience.

What is quite interesting about what you're trying to do here is that it's very much akin to Normalising your database, which is a Very Good Thing(tm). For more on that, and I would strongly advise to look at that somewhen, even if it's not too important to this particular thread, see Database Normalisation and Table Structures which is a great introduction to a set of concepts that underpin (or certainly should) all database work. On that point, the total lines wouldn't be considered Normalised. Are you sure they are actually necessary for you?

Using SQL may be possible here, but would certainly be awkward. I'm not saying don't, but I expect VBA would be easier for all concerned. At this point I'll request you post your latest version of code, as amended by the ideas from ADezii, and say what goes wrong and wher. Use line numbers as displayed in the code window on here to identify where the error message is triggered.

[NeoPa]

HibHib:
The Format of the worksheets in the excel file will ALWAYS be

That's helpful, but it raises another important question which you'll understand the significance of better once you've read the Normalisation link from my last post.

What is an Employee?
We have it identified separately here as [First] + [Last] as well as simply [Employee]. I suspect there may well be (Should be really.) a separate table that contains Employees. Thus all references would need to be converted somehow to references to that table. Is this all appreciated and handled in your project so far?

More things to worry about, I know, but that's what this game's all about. The trick being to break each problem down into bite-sized chunks and sort them all out one after the other. Until, suddenly, it's all done and it all works as required. Always a good feeling.

[ADezii]

The Key for me was Post# 13 where you posted both the pre and post Data. I am still hazy on a few points but I have created what I feel is at least a close approximation to a solution. Notice the Optimization of the Recordsets as well as the aggregation on the [Resourced] Values (not really sure on this). I will not add to an already lengthy Thread, but simply Attach what I have come up with in the hope that I am at least in the ballpark.

[HibHib]

Ill post the code tomorrow as it is on my work computer, which they dont let me take with me.
"On that point, the total lines wouldn't be considered Normalised. Are you sure they are actually necessary for you?" May you clarify what you mean by this?
Also, the [Employee] field on the table im appending to will be populated by [First] + [Last] from the Worksheets
On the topic of normalization, It would be really nice if i could normalize everything, but i don't believe the company has any plans to update the sheet from within access, and will more elect to just upload an updated excel file whenever they want updated graphs (much to my dismay)

[HibHib]

@ADezii
Wow, Thank you for taking so much of your time to create this, it is certainly withing the ballpark of what i am trying to accomplish, i will certainly make use of this

[ADezii]

I feel as though I am at the End-of-the-Line as far as what more relevant input I can provide given my understanding of the situation. Hopefully, I have provided enough for you to take over on your own. Good Luck with your Project.

[PhilOfWalton]

NeoPa is h about normalisation in as much as you need 3 tables - Employees, Projects and JoinEmployeePro ject to hold the resource.

You need to populate the Employee table first, then the project talbe.
ADezil's method will be close to populating the join table.

Phil

[NeoPa]

HibHib:
"On that point, the total lines wouldn't be considered Normalised. Are you sure they are actually necessary for you?" May you clarify what you mean by this?

Of course. It will help if you've read and understood the linked article.
With normalisation, one of the key principles is that the same data is never stored more than once. That includes in aggregated form (IE. Totals or averages etc). The total lines seem to be simple aggregates of the rest of the data for the group so, as such, are not normalised. You can recreate them from the other data as all the information is already there.

HibHib:
Also, the [Employee] field on the table im appending to will be populated by [First] + [Last] from the Worksheets

I suspected as much, or something similar. An [Employee] table would be preferable in a normalised environment but I can see you're working under limitations. What you have should be perfectly workable.

HibHib:
On the topic of normalization, It would be really nice if I could normalize everything, but I don't believe the company has any plans to update the sheet from within access, and will more elect to just upload an updated excel file whenever they want updated graphs (much to my dismay)

I hear you. Normalisation is great. Really great actually. However, there are situations where it offers less than others and you're already working to a specific requirement from your bosses. I believe what you need can be done without worrying unduly about normalisation.

Please take a look at ADezii's new post first off. When you've incorporated anything from that which is helpful then post your updated code if you still need to.

Notice that ADezii has done what he's done in spite of not having a clear understanding of the whole problem. Imagine the extra benefits of clearer questions. You'll be impressed. BTW I'm not trying to moan here, or even beat a dead horse. I'm simply selling the idea that I expect you to benefit from.

[HibHib]

Does Dao Recordet save old records? Because when i incorporated ADezii's ideas, the code ran but ended up putting 4 times as many records as i currently have, including ones from old tables that i had deleted long ago, severely messing up the table im appending to.

[ADezii]

1. Though I still feel that I can add nothing new to this Thread, I surely would like to comment on existing Code, especially when it is my own (LOL).
2. The DAO Recordset (rst) will not persist Data each time it is Instantiated. For every Table, except System Tables and the Result Table, a new Recordset is created based on that Table only. There is no 'residual Data' that persists from the previous time that it was created. Reference partial Code Segment below:
   

   '****************** CODE INTENTIONALLY OMITTED ******************
   For Each tdf In MyDB.TableDefs
     'Don't want System Tables or Results Table (Result)
     If Left$(tdf.Name, 4) <> "MSys" And tdf.Name <> "Result" Then
       Set rst = MyDB.OpenRecordset(tdf.Name, dbOpenForwardOnly)
   '****************** CODE INTENTIONALLY OMITTED ******************

3. If you are getting more Data than you should, I can think of 2 possible reasons:
4. You need to add other Filtering Conditions to your Tables beside Non-System and the Result Table:
   

   If Left$(tdf.Name, 4) <> "MSys" And tdf.Name <> "Result" Then

5. You have not included the Line of Code that clears the Result Table each time the Code is executed. This most definitely is the problem since it must have been inadvertently DELETED when I Uploaded the Demo. After
   

   Set rstResults = MyDB.OpenRecordset("Result", dbOpenDynaset, dbAppendOnly)

6. add this line which will clear the Result Table prior to populating it. Without this line of Code the Result Table will continue to grow each time it is executed.
   

   CurrentDB.Execute "DELETE * FROM Result", dbFailOnError

7. This should clearly resolve the issue, sorry.

At this point I'll request you post your latest version of code, as amended by the ideas from ADezii

There is a reason why NeoPa made this request. If I would have seen the Code I would have known immediately what the problem was.

[HibHib]

I had already added the delete function in and that still didnt solve the issue, and when i tried adding the filter of

Right$(tdf.name, 5) = "Input"

i got and error on

rst.Close

Object variable or With block variable not set. I had the table names printed and they displayed as "~TMPCLP### ##" which i believe is a cached tbl name??
Edit: got the thing to close properly, but because of my filter, the fact that the names of the tables display as "~TMPCLP" it ends up putting in nothing, its odd that my actual tables arent showing.
My reasoning for not displaying the code is due to the code working fine, just grabbing tables that no longer exist

[ADezii]

Not sure what the root of this problem is, try:

If Left$(tdf.Name, 4) <> "MSys" And tdf.Name <> "Result" And Left$(tdf.Name, 1) <> "~" Then

[HibHib]

Well ill be darned, that worked, even though i didnt think it was fundamentally different from Right$(tdf.Name , 5) = "Input" evidently it is. Thank you

[ADezii]

You are quite welcome, out of curiosity, when was the last time you ran a Compact & Repair on the DB?

[HibHib]

Right after i tried to find solutions to the problem, looked it up before coming to you for help and saw that that may have been the issue, didn't seem to help however