By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

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

P: 19
I am reformatting an excel sheet to be more access friendly and had to use a recordset, however i have multiple tables that im trying to reformat into 1 new table using vba. When using the function recordset.open the first parameter is Source, and is where i put a table name. To get this to work i tried to establish a loop where it would go through tab1, tab2, tab(n) but i kept getting errors. Is it possible to loop through table names in this function? Access 2007

Expand|Select|Wrap|Line Numbers
  1.     Dim db As DAO.Database
  2.     Dim rst As New ADODB.Recordset
  3.     Set db = CurrentDb
  4.     Dim x As Integer
  5.     Dim tb As String
  6. For x = 1 To db.TableDefs.Count
  7.     tb = "tab" & "x"
  8.     rst.Open tb, CurrentProject.Connection, adOpenDynamic
  9. Next x
Sorry if the answer is obvious, im rather new to VBA
Jun 21 '17 #1

✓ answered by 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.

Share this Question
Share on Google+
31 Replies


PhilOfWalton
Expert 100+
P: 1,430
Don't know but certainly "tab" & "x" will always give a string "tabx".

Next problem is that the TableDefs collection includes system & hidden files such as "MsysObjects" & "MSysRelationships" Probably about 13 system tables.

Next very minor problem is that the TableDefs count starts at 0.

I have no idea if this will work, but it's closer

Expand|Select|Wrap|Line Numbers
  1.  Dim db As DAO.Database
  2.     Dim rst As New ADODB.Recordset
  3.     Set db = CurrentDb
  4.     Dim i As Integer
  5.     Dim TableName As String
  6. For i = 0 To db.TableDefs.Count - 1
  7.     TableName = Currentdb.TableDefs(i).Name
  8.     If Left(TableName, 4) <> "Msys" then
  9.         rst.Open TableName, CurrentProject.Connection, adOpenDynamic
  10.     End If
  11. Next i
  12.  
Phil
Jun 21 '17 #2

P: 19
Seems a lot closer to what im wanting, and more flexible, however it returns the same error of "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'." which seems odd do to it working if there's a standard table name there, thank you though
Jun 21 '17 #3

NeoPa
Expert Mod 15k+
P: 31,418
I'm sorry to say this looks a bit untidy. I can't see what you're trying to do. Nothing that I can think of that you may be trying to do makes sense.

OTOH, your immediate problem appears to be trying to use positional parameters in the wrong position.

My advice is to use named parameters where possible. It leads to fewer errors and it's more self-documenting. Who wants to have to work on someone's code that lists the parameters to multi-parameter procedures? Not fun.

Where you go from there is another issue of course.
Jun 22 '17 #4

P: 19
I may be making this way more complicated than it should be, but basically i want to be able to reformat multiple tables and export them into 1 table, my code atm looks like this, but i get the error of "Cannot Find the input 'tableName'.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command1_Click()
  2.     Dim db As DAO.Database
  3.     Dim rst As New ADODB.Recordset
  4.     Set db = CurrentDb
  5.     Dim tbl As TableDef
  6.     Dim Reset As String
  7.     Reset = "Delete From result"
  8.  
  9.     db.Execute Reset
  10.     Dim i As Integer
  11.     Dim TableName As String
  12. For i = 0 To db.TableDefs.Count - 1
  13.     TableName = db.TableDefs(db.TableDefs.Count - 1).Name
  14.     If Left(TableName, 4) <> "Msys" Then
  15.         rst.Open TableName, CurrentProject.Connection, adOpenDynamic
  16.  
  17.     Dim n As Integer
  18.     Dim fieldname As String
  19.     Dim str As String
  20.  
  21. For n = 2 To rst.Fields.Count - 1
  22.     fieldname = rst.Fields(n).Name
  23.     If InStr(rst.Fields(n).Name, " ") = 0 Then
  24.     End If
  25.     str = "Insert Into Result(Employee, Project, Resourced) SELECT [First] As Employee, " & Chr(34) & " " & fieldname & Chr(34) & " " & " As Project," & "TableName." & fieldname & " As Resourced FROM TableName;"
  26.     db.Execute str
  27.  
  28.  
  29. Next n
  30. End If
  31. Next i
  32. End Sub
This is what i am trying to do with the loop
Jun 22 '17 #5

ADezii
Expert 5K+
P: 8,623
I agree with NeoPa on this one in that very little of what you have shown makes much sense. It appears as though your goal is to: reference every Field except the 1st and 2nd
Expand|Select|Wrap|Line Numbers
  1. For n = 2 To rst.Fields.Count - 1 
in every Non-System Table,
Expand|Select|Wrap|Line Numbers
  1. If Left$(tbl.Name, 4) <> "MSys"
check each Field Name to see if it has a Space in the Name
Expand|Select|Wrap|Line Numbers
  1. If InStr(rst.Fields(n).Name, " "), = 0
  2.   'nothing happening here, Code will fall through
  3. End If
  4.  
then perform some sort of Append Operation if it does.
Expand|Select|Wrap|Line Numbers
  1. str = "Insert Into Result(Employee, Project, Resourced) SELECT [First] As Employee, " & Chr(34) & " " & fieldname & Chr(34) & " " & " As Project," & "TableName." & fieldname & " As Resourced FROM TableName;"
  2. db.Execute str
  3.  
Am I even close in what I am seeing?
Jun 22 '17 #6

P: 19
I am making graphs based off a 12 worksheet excel file, the issue is that the excel files format isnt, to my knowledge, in a way i can make the variable graphs, so im taking the tables that i imported from an import code, and reformatting them so that i can make the graphs correctly in my report.The criteria for the graphs is the project and resourced time for each employee so im taking the field names (starts at 3 because the first 2 are names) which are the project labels, and put them down the column "projects" then i put the info linked to the project down the column resourced with "TableName".fieldname from "TableName", that part of the code works fine IF i only do it with one table, but when i try to make it different tables i come across issues
Edit: Also the check for spaces thing is something i removed, that was there before i fixed a previous issue, i just didnt realize it was still there in this iteration of my code, doesnt effect anything related to the issue however
Double edit: Also i realize the issue im having has shifted, checking the code
Expand|Select|Wrap|Line Numbers
  1.  For i = 0 To db.TableDefs.Count - 1
  2.     TableName = db.TableDefs(db.TableDefs.Count - 1).Name
  3.     If Left(TableName, 4) <> "Msys" Then
  4.         rst.Open TableName, CurrentProject.Connection, adOpenDynamic
This works just fine but
Expand|Select|Wrap|Line Numbers
  1.  str = "Insert Into Result(Employee, Project, Resourced) SELECT [First] As Employee, " & Chr(34) & " " & fieldname & Chr(34) & " " & " As Project," & "TableName." & fieldname & " As Resourced FROM TableName;"
  2.     db.Execute str
This isnt reading TableName as a variable and instead is searching it as "TableName" being the name of the table
Jun 22 '17 #7

ADezii
Expert 5K+
P: 8,623
Does each Non-System Table have the same number of Fields, and are they in the same Ordinal Position?, eg.
Expand|Select|Wrap|Line Numbers
  1. Field(3)-[Employee]
  2. Field(4)-[Project]
  3. Field(5)-[Resourced]
Jun 22 '17 #8

P: 19
Yes, but the format of the first tables different
Expand|Select|Wrap|Line Numbers
  1. Field(1)-[First]
  2. Field(2)-[Last]
  3. Field(3)-[Project_A]
  4. Field(4) - [Project_B]
  5. `etc
The one table im appending all of the tables to is in that format, the reason im going this seemingly more difficult way rather than import them all into 1 table in the first place is after this im going to be adding more code that creates a month field that distinguishes which month things happened in (each worksheet being a different month)
Jun 22 '17 #9

ADezii
Expert 5K+
P: 8,623
I see no Method to Loop thru the Records in each Recordset, are they consistent in each Table (Same number of Records)?
Jun 22 '17 #10

P: 19
Yes, Same number of records in each table
Jun 22 '17 #11

ADezii
Expert 5K+
P: 8,623
Given the consistencies that you have indicated, except for a single Table, I feel as though the following Code is at least viable for all the other Tables. A special provision can be made for the single Table. Given the circumstances, I also felt that the Fields Loop for each Recordset is not required since you are only addressing Fields 2, 3, and 4. If I am incorrect in my assumptions, I do apologize since my intent is not to confuse.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Set db = CurrentDb
  4. Dim tbl As DAO.TableDef
  5. Dim i As Integer
  6. Dim n As Integer
  7. Dim TableName As String
  8. Dim rstResults As DAO.Recordset
  9.  
  10. 'Clear the Results Table
  11. CurrentDb.Execute "DELETE * FROM Result", dbFailOnError
  12.  
  13. Set rstResults = db.OpenRecordset("Result", dbOpenDynaset)
  14.  
  15. For Each tbl In db.TableDefs
  16.   If Left$(tbl.Name, 4) <> "MSys" And tbl.Name <> "Result" Then      'Non-System Table and
  17.     TableName = tbl.Name                                             'not the Results Table
  18.       Set rst = db.OpenRecordset(TableName, dbOpenSnapshot)
  19.         Do While Not rst.EOF
  20.           With rstResults
  21.             .AddNew
  22.               ![Employee] = rst.Fields(2)
  23.               ![Project] = rst.Fields(3)
  24.               ![Resourced] = rst.Fields(4)
  25.             .Update
  26.             rst.MoveNext
  27.           End With
  28.         Loop
  29.   End If
  30. Next
  31.  
  32. rst.Close
  33. db.Close
  34. rstResults.Close
  35. Set rst = Nothing
  36. Set rstResults = Nothing
  37. Set db = Nothing
P.S. - I felt as though an entire revamp of the Code was necessary.
Jun 22 '17 #12

P: 19
There is a bit of a misunderstanding, though i will incorporate a some of what you did because its cleaner code
What my code does is turn this
Expand|Select|Wrap|Line Numbers
  1. Last    First    Utilization    Project A    Project B    Project C    Project D    Project E    Project F    Project G    Project H
  2. Employee 1    Employee 1    95%    0.2    0.25    0.1    0.3    0.1    0    0    0
  3. Employee 2    Employee 2    73%    0    0    0    0    0.36    0.17    0.2    0
  4. Employee 3    Employee 3    80%    0    0.1    0.5    0    0    0.1    0    0.1
  5. Employee 4    Employee 4    95%    0.35    0    0    0    0    0    0.1    0.5
  6. Employee 5    Employee 5    60%    0.1    0    0    0.15    0    0.35    0    0
  7. Employee 6    Employee 6    88%    0    0.38    0.35    0    0.05    0.1    0    0
  8. Employee 7    Employee 7    68%    0    0    0.05    0    0.1    0    0.38    0.15
  9. Employee 8    Employee 8    95%    0.5    0.4    0    0    0    0    0    0.05
  10. Employee 9    Employee 9    100%    0.1    0    0.1    0.2    0.6    0    0    0
  11.  
Into
Expand|Select|Wrap|Line Numbers
  1. Employee    Project    Resourced
  2. Employee 1    A    0.17
  3. Employee 1    C    0.2
  4. Employee 1    D    0.5
  5. Employee 1    Utilization    0.87
  6. Employee 2    A    0.1
  7. Employee 2    B    0.2
  8. Employee 2    C    0.3
  9. Employee 2    D    0.4
  10. Employee 2    Utilization    1
  11. Employee 3    A    0.1
  12. Employee 3    B    0.05
  13. Employee 3    C    0.2
  14. Employee 3    F    0.1
  15. Employee 3    G    0.5
  16. Employee 3    Utilization    0.95
  17. Employee 4    B    0.3
  18. Employee 4    C    0.25
  19. Employee 4    E    0.35
  20. Employee 4    Utilization    0.9
  21. Employee 5    A    0.15
  22. Employee 5    B    0.5
  23. Employee 5    D    0.05
  24. Employee 5    E    0.1
  25. Employee 5    H    0.15
  26. Employee 5    Utilization    0.95
  27. Employee 6    D    0.2
  28. Employee 6    F    0.38
  29. Employee 6    G    0.35
  30. Employee 6    Utilization    0.93
  31. Employee 7    B    0.3
  32. Employee 7    C    0.6
  33. Employee 7    G    0.05
  34. Employee 7    Utilization    0.95
  35. Employee 8    A    0.19
  36. Employee 8    B    0.2
  37. Employee 8    D    0.6
  38. Employee 8    Utilization    0.99
  39. Employee 9    D    0.05
  40. Employee 9    E    0.1
  41. Employee 9    G    0.1
  42. Employee 9    H    0.7
  43. Employee 9    Utilization    0.95
The number of fields is the same across each table but that total number can change(dependent on if new projects are added), sorry for the misunderstanding and the misalignment of the text (also the different numbers, this is copy and pasted from 2 different iteration of my work)
Jun 22 '17 #13

NeoPa
Expert Mod 15k+
P: 31,418
@HibHib.
Firstly, please see the PM I sent you (https://bytes.com/private.php).

Now, as this seems to be a whole project-wide question (unfortunately), can you explain where the data is at the time this code is supposed to run? I'm guessing that you've already imported the data from your separate worksheets into Access. This is critically important to the question, but doesn't appear to have been included.

If they are already imported then what do you know about the names of the separate tables in the Access database? It's very likely that much of the work can be done in SQL instead of the more laborious VBA approach. To determine the best approach we'd need a good understanding of what you're working with. Please include what you know about the situation. Is the number of worksheets in the workbook always identical? Are you confident that the data will always be in the same format? Are all worksheets in the same format as each other?

VBA can work with recordsets, and even transactions, but it can make it complicated. Some prefer that to using SQL. Others prefer SQL. SQL is certainly more succinct in most cases.
Jun 22 '17 #14

P: 19
They are, or in future cases, will be already imported and the names of the tables are based on the worksheet names, which should always be MAY INPUT, JUN INPUT, JUL INPUT, etc. The number of worksheets can change depending on when the excel file is uploaded because the company deletes the months that have passed. The format of every worksheet will be the same but may have a few more columns depending on what projects exist at the time of the upload, but the column count is the same across all worksheets at that time. The reason im using recordsets is because i don't know any other method to have field names list down a column to be more access friendly
Sorry for extending this post past its original question
Edit: The Format of the worksheets in the excel file will ALWAYS be
Expand|Select|Wrap|Line Numbers
  1. Last   First  Utilization    Project_1 Project_2 ... Project_N
And the format i am trying to get them to in one table is
Expand|Select|Wrap|Line Numbers
  1. Employee Project Resource
Where project lists the field names from the original tables, and resourced is the value linked to that
From there i am taking this table and putting it into a report(that i have already made) that has a graph based on this info per employee. From what i have researched, the access table needs to be formatted like this in order to graph project by resourced.
Jun 22 '17 #15

NeoPa
Expert Mod 15k+
P: 31,418
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.
Jun 22 '17 #16

NeoPa
Expert Mod 15k+
P: 31,418
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.
Jun 22 '17 #17

ADezii
Expert 5K+
P: 8,623
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.
Attached Files
File Type: zip Demo.zip (25.4 KB, 32 views)
Jun 22 '17 #18

P: 19
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)
Jun 22 '17 #19

P: 19
@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
Jun 22 '17 #20

ADezii
Expert 5K+
P: 8,623
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.
Jun 22 '17 #21

PhilOfWalton
Expert 100+
P: 1,430
NeoPa is h about normalisation in as much as you need 3 tables - Employees, Projects and JoinEmployeeProject 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
Jun 22 '17 #22

NeoPa
Expert Mod 15k+
P: 31,418
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.
Jun 22 '17 #23

P: 19
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.
Jun 23 '17 #24

ADezii
Expert 5K+
P: 8,623
  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:
    Expand|Select|Wrap|Line Numbers
    1. '****************** CODE INTENTIONALLY OMITTED ******************
    2. For Each tdf In MyDB.TableDefs
    3.   'Don't want System Tables or Results Table (Result)
    4.   If Left$(tdf.Name, 4) <> "MSys" And tdf.Name <> "Result" Then
    5.     Set rst = MyDB.OpenRecordset(tdf.Name, dbOpenForwardOnly)
    6. '****************** CODE INTENTIONALLY OMITTED ******************
    7.  
  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:
    Expand|Select|Wrap|Line Numbers
    1. 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
    Expand|Select|Wrap|Line Numbers
    1. 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.
    Expand|Select|Wrap|Line Numbers
    1. 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.
Jun 23 '17 #25

P: 19
I had already added the delete function in and that still didnt solve the issue, and when i tried adding the filter of
Expand|Select|Wrap|Line Numbers
  1. Right$(tdf.name, 5) = "Input"
i got and error on
Expand|Select|Wrap|Line Numbers
  1. 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
Jun 23 '17 #26

ADezii
Expert 5K+
P: 8,623
Not sure what the root of this problem is, try:
Expand|Select|Wrap|Line Numbers
  1. If Left$(tdf.Name, 4) <> "MSys" And tdf.Name <> "Result" And Left$(tdf.Name, 1) <> "~" Then
Jun 23 '17 #27

P: 19
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
Jun 23 '17 #28

ADezii
Expert 5K+
P: 8,623
You are quite welcome, out of curiosity, when was the last time you ran a Compact & Repair on the DB?
Jun 23 '17 #29

P: 19
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
Jun 23 '17 #30

NeoPa
Expert Mod 15k+
P: 31,418
If I remember correctly, deleting a table doesn't delete it so much as renaming it to something starting with a tilde (~) and ensuring it no longer shows in the list of tables via the interface. Access does little to no garbage collection off its own bat. All is done when, and only when, a C&R is performed. This should always be done regularly, but with backups.

To illustrate the point :
  1. Backup your database.
  2. Copy and Paste one of your tables and call it [zzzTest].
  3. Delete table [zzzTest].
  4. Open the VBAIDE and go to the Immediate Pane (Ctrl-G).
  5. Paste in the following line of code :
    Expand|Select|Wrap|Line Numbers
    1. For Each T in CurrentDb().TableDefs:?T.Name;";";:Next
  6. Press {Home} to see the first in the list.
  7. It'll be of the form [~TMPCLP######], where #s refer to digits.
  8. Restore your original database.
Jun 24 '17 #31

NeoPa
Expert Mod 15k+
P: 31,418
HibHib:
even though i didnt think it was fundamentally different from Right$(tdf.Name, 5) = "Input" evidently it is.
No. Something else has happened that you haven't spotted. Not that it matters now for this case, but don't let that idea be what you take away from this experience.

Also, it may be interesting to note that TableDefs have more properties than simply the Name. From those you could exclude various types of table too. That said, a Name ending in "Input" should be all you should need I would assume.

BTW Good to see you have a working version.
Jun 24 '17 #32

Post your reply

Sign in to post your reply or Sign up for a free account.