Using VBA to search all tables in a database

[NL111]

Wow awesome, it works like a charm. Sorry for asking again but when I click Search, the first boxes that displays have this as the Table name: ~TMPCLP249751 where the 249751 are random numbers based on the table. After clicking OK to those, it will display just the names just fine.

 

[AccessMSSQL]

exclude any tables that begin with ~TMP. What do your table names begin with. If you are naming them conventionally with "tbl" as the prefix than you can exclude all other tables in the search except tbl.

For Each tdf In CurrentDb.TableDefs
   if left(tdf.Name, 3) = "tbl" then
    sTable = tdf.Name
   .....

OR

For Each tdf In CurrentDb.TableDefs
if left(tdf.Name, 3) <> "~TM" then
sTable = tdf.Name
.....

 

[NL111]

I have 3 tables right now with names such as 20120629 , 20120802, and 20120909. However, I have 3 tables that I have just deleted off.

Edit; I have just restarted Microsoft Access, and it seems to be fine now. Thank you to EVERYONE who has helped me along the way, this will help me a lot in the future.

As a side note, is it possible for the results to be displayed inside not a pop up box but inside a text box? it might make looking at all the results much easier to look at.

 

[AccessMSSQL]

Yes. Add a textbox to your main form. and do something like this - changes in red

Public Sub SearchTables(SearchString As String)
Dim tdf As DAO.TableDef
Dim sTable As String
Dim sField As String
[COLOR=red]Dim sMsg as String[/COLOR]
sMsg = ""
For Each tdf In CurrentDb.TableDefs
    sTable = tdf.Name
    sField = SearchTable(sTable, SearchString)
    If sField <> vbNullString Then
        [COLOR=red]sMsg = sMsg & vbcrlf & "Table:" & sTable & " Field:" & sField
[/COLOR]    End If
Next
[COLOR=red]forms!YourSearchFormName!txtResult = sMsg[/COLOR]
End Sub

 

[AccessMSSQL]

Make sure your textbox is vertically large enough and has scrollbars=vertical

 

[NL111]

Awesome. Thank you very much

 

[jdraw]

I'm glad you have an "awesome" solution to this issue.
Just for those of us who remain curious, could you tell us in a couple of sentences When and Why you need this functionality?

Thanks in advance.

 

[NL111]

This form will be used in a database for shipments made on a day. We have each table as the date, with which location it is going to, and maybe which truck it is on. Inside each of the table there are part numbers, quantity, and any other numbers which needs to be included. Sometimes, I might need to look for a part number which is in 1 of the tables, and with this, I can find it. There might be also other things to search for , and this form will accomplish that.

There is also another thing I want to do, but I am afraid it might take a very long time. I want to find out to see if there are any duplicates in certain columns inside all the tables, as there isn't supposed to be 2 identical part number.

 

[jdraw]

Thanks very much for replying. I'm happy that this solution fits your needs. I still recommend you do some research and experimentation with Normalization. I'm certain you will save yourself a lot of effort when you have designed your tables according to Relational Database Principles.

That's not to say that what you have won't work - as you have seen it does work.
The analogy I see in my mind is like using a tool like an electric drill as a hammer because you need to hit something and the drill is all you have.

Good luck with your project.

 

[NL111]

Thank you for all your help on this. I will certainly take a look at Normalization, but as of right now, I don't really see any other way as the way the tables are formatted is set in stone by the company, I cannot change that.
I understand that the solution I got isn't the most efficient, but for this usage, it is fine for me. All I needed to do was to import the excel files, choose the names, and that is good enough for us to use.

 

[Galaxiom]

Where a developer has no control over a denormalized data structure, sometimes the best approach is to write queries to normalize it then work from that new structure. This is especially so when just reporting because there is no need to pass data back to the denormalized structure.

This avoids the need for repeated workarounds each time you encounter the problems caused by the original structure.

 

[spikepl]

You have apparently fallen victim to the fairly common misunderstanding that the data structure - the way in which data is stored inside Access - must in some way reflect the format/layout/way in which it is input or output.

It doesn't. As jdraw mentioned, insisting on keeping your structure as-is will make anything very painfull. Evidence? Just how much effort went into the 50 posts in this thread, which would not exists had your data been properly normalized.

Read up on normalization., and stuff the data stemming from the spreadsheets into proper normalized tables.

 

[RainLover]

Have you explained to your boss what the problem is.

If he understands databases then he would appreciate you advice.

If he does not understand then show him. After all if you are the programmer then it is your duty to keep your supervisor well informed.

 

[NL111]

The main problem which I am having is this. The format set by the company is not really set by us, but it is from our client, who chooses the file. I have been asked to create this database for our own internal use, and for external, we have to upload the original files to the client, who uses a different software to sort it into their system. That is why this is so hard to change.

On another note, I have a question. How would I make it so that if I click the name of the table it will open the table up for me? It is just quite a bit of work right now as you have to scroll down, find the name of the table, then open it. Any help will be appreciated.

 

[spikepl]

To call it as I see it: your main problem is that you are completely stuck in a fallacy, despite multiple attempts in this thread to get you out of it.

THE WAY DATA IS ORGANISED/FORMATTED OUTSIDE ACCESS OR PRESENTED IN ACCESS HAS NO BEARING ON HOW IT SHOULD BE STORED INSIDE ACCESS

 

[NL111]

Yes I understand that. I have another problem which is that I will not be working in this company for long, as I am only a trainee here. Thus I am worried what will happen after I leave. This is why I have tried to minimize the amount of change there are from the original file. My boss also only wants the layout to be similar/the same as the original file, which further limits what I can do.

 

[jdraw]

Have you discussed with your boss the information and concepts you are receiving, and repeated attempts to move away from the status quo?

 

[spikepl]

I am not quite sure whether or not you understand what you are getting into, but perhaps another line of arguments works better: there is no "Normalization police" to punish anyone for not normalizing the data. However there is a penalty to be paid, directly in dollars and cents every month, in increased maintenance costs.

Your argument that you are leaving makes no sense here: making the data structure sound would make any subsequent changes or additions easy. With current "structure" any change or addition will be expensive.

How your boss wants to see a layout is totally irrelevant (this should probably be said to him or her in a more convoluted way). Once again: the presentation of data is not related to how it is stored, and therefore it should be stored sensibly.

 

[jdraw]

See http://www.youtube.com/watch?v=i72fVNqfWL4