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

help writing a multiple table query

100+
P: 164
I really need some direction writing a query here.

I provided a screen cap so you may catch the drift.

http://www.mykesdesigns.com/tblrock1...s/terminfo.jpg

as you may see there are a ton of tables. there are 58 tables that are all nearly identical to the one shown...

each table represents a txt file that is imported every night to our server and each txt file is a store. These files hold the info about the terminals..

Every day I sift through these same 60 files and look to make sure #2 in the TermName field is set to Backup in the Role field, and also that no other Terms have the Role backup in the field.

I would really love some direction to take on how to make a query output a "REDFLAG" if you will, pointing out that a sertain table is not how it should be.


I hope this makes sense. Ill explain anything better that anyone wants me to.

Thanks a ton in advance!
Oct 24 '07 #1
Share this Question
Share on Google+
112 Replies


nico5038
Expert 2.5K+
P: 3,072
It would be best to create a function that processes all tables and performs the checks needed. Are you able/willing to use VBA code ?
Is the Backup-role test all that's needed, or do you use the data for more ?

Nic;o)
Oct 24 '07 #2

100+
P: 164
It would be best to create a function that processes all tables and performs the checks needed. Are you able/willing to use VBA code ?
Is the Backup-role test all that's needed, or do you use the data for more ?

Nic;o)
heyy I would be willing to whatever is necessary, the database is only being used for this function.

Sometimes these locations have two terminals set as backup, and sometimes they dont have a backup.

what I need it to be able to run it and have it return anything that is not how it should be so I can email them and tell them to fix it. whether that be they have two terminals on back up or none on backup.

Ill even post this database on the internet if that helps...

Please get back to me!
thank you!
Oct 24 '07 #3

nico5038
Expert 2.5K+
P: 3,072
No need, the function isn't that hard.

Expand|Select|Wrap|Line Numbers
  1. function fncFindBackup()
  2. ' these dim's will need you to set a reference to the "Microsoft DAO version 3.##" library
  3. ' and remove the "Microsoft Active Data Objects" library when selected.
  4. ' use Tools/References to (un)set them
  5. dim td as DAO.Tabledef
  6. dim rs as DAO.Recordset
  7. ' The results are stored in a temp table tblWarnings
  8. ' Create that table with one 250 char text field "Warning"
  9. dim rsF as DAO.recordset 
  10.  
  11. ' initialize the warnings
  12. currentdb.execute ("delete * from tblWarning")
  13.  
  14. set rsF = currentdb.openrecordset("tblWarnings")
  15.  
  16. ' Process all tables
  17. for each td in currentdb.tabledefs
  18.    ' check the name to "fit"
  19.    if left(td.name,8) = "terminfo" then
  20.       ' get all records for 'Backup' role
  21.       set rs = currentdb.openrecordset(select * from " & td.name & " where Role ='Backup'")
  22.           if rs.eof and rs.bof then
  23.              ' Nothing found
  24.              rsF.addnew
  25.              rsF!Warnings = "Table: " & td.name & " has no backup"
  26.              rsF.Update
  27.           else
  28.              ' move to end for getting correct count
  29.              rs.movelast
  30.              ' test one record
  31.              if rs.recordcount > 1 then
  32.                 rsF.addnew
  33.                 rsF!Warnings = "Table: " & td.name & " has " & rs.recordcount & " backups"
  34.                 rsF.Update
  35.               else
  36.                  'one record found, now test "02=" in start of [Terminal Info] field
  37.                  if left(rs![Terminal Info],3) <> "02=" then
  38.                    rsF.addnew
  39.                    rsF!Warnings = "Table: " & td.name & " has no backup in '02='"
  40.                    rsF.Update
  41.                  endif 
  42.              endif   
  43.           endif
  44.    endif
  45. next
  46.  
  47. end function
Just read the comment to see what's happening. The code needs to be copy/pasted into a module (E.g. "modWarnings")

Getting the idea ?

Nic;o)
Oct 24 '07 #4

100+
P: 164
EDIT- 3:48
I can safely say that I am CONFUSED....

I tried doing that..http://www.mykesdesigns.com/tblrock1/access/module.jpg

I dont know if I know what exactly what to do, but I hit run and then get this?
http://www.mykesdesigns.com/tblrock1/access/module2.jpg

Please forgive me for being ridiculously dumb, I am trying to read up on it but time is NOT of the essence and I thank you very much!



No need, the function isn't that hard.

Expand|Select|Wrap|Line Numbers
  1. function fncFindBackup()
  2. ' these dim's will need you to set a reference to the "Microsoft DAO version 3.##" library
  3. ' and remove the "Microsoft Active Data Objects" library when selected.
  4. ' use Tools/References to (un)set them
  5. dim td as DAO.Tabledef
  6. dim rs as DAO.Recordset
  7. ' The results are stored in a temp table tblWarnings
  8. ' Create that table with one 250 char text field "Warning"
  9. dim rsF as DAO.recordset 
  10.  
  11. ' initialize the warnings
  12. currentdb.execute ("delete * from tblWarning")
  13.  
  14. set rsF = currentdb.openrecordset("tblWarnings")
  15.  
  16. ' Process all tables
  17. for each td in currentdb.tabledefs
  18.    ' check the name to "fit"
  19.    if left(td.name,8) = "terminfo" then
  20.       ' get all records for 'Backup' role
  21.       set rs = currentdb.openrecordset(select * from " & td.name & " where Role ='Backup'")
  22.           if rs.eof and rs.bof then
  23.              ' Nothing found
  24.              rsF.addnew
  25.              rsF!Warnings = "Table: " & td.name & " has no backup"
  26.              rsF.Update
  27.           else
  28.              ' move to end for getting correct count
  29.              rs.movelast
  30.              ' test one record
  31.              if rs.recordcount > 1 then
  32.                 rsF.addnew
  33.                 rsF!Warnings = "Table: " & td.name & " has " & rs.recordcount & " backups"
  34.                 rsF.Update
  35.               else
  36.                  'one record found, now test "02=" in start of [Terminal Info] field
  37.                  if left(rs![Terminal Info],3) <> "02=" then
  38.                    rsF.addnew
  39.                    rsF!Warnings = "Table: " & td.name & " has no backup in '02='"
  40.                    rsF.Update
  41.                  endif 
  42.              endif   
  43.           endif
  44.    endif
  45. next
  46.  
  47. end function
  48.  
Just read the comment to see what's happening. The code needs to be copy/pasted into a module (E.g. "modWarnings")

Getting the idea ?

Nic;o)
Oct 24 '07 #5

nico5038
Expert 2.5K+
P: 3,072
Guess the error was related to the linenumbers that shouldn't be copied.
Thus I've removed the "code-tags" in my previous comment so you have the plain text needed.

After saving the function place the textcursor on a command and press F8 to start excuting the first line. Then press F8 again for the next line, etc.
That way you can see what how code works and give me the line and message when you run into an error.

Nic;o)
Oct 24 '07 #6

100+
P: 164
I hope I am doing all of this correctly!


http://www.mykesdesigns.com/tblrock1/access/module3.jpg
Oct 24 '07 #7

nico5038
Expert 2.5K+
P: 3,072
Sorry, forgot a " before the SELECT, try:

Set rs = CurrentDb.OpenRecordset("select * from " & td.Name & " where Role ='Backup'")

Nic;o)
Oct 24 '07 #8

100+
P: 164
OKay so now I got two errors telling me to create tblwarning and tblwarnings

I did that... and now I run the code. and nothing happens??

I dont have any errors though!
Oct 24 '07 #9

nico5038
Expert 2.5K+
P: 3,072
Oops, typo, use:
currentdb.execute ("delete * from tblWarnings")
and make sure only a tblWarnings is in your database.

Now use the F8 to "walk" through the code to detect where errors are found. Each error should be found in the tblWarnings, but when all's OK it will be empty...

Nic;o)
Oct 24 '07 #10

100+
P: 164
okay, so I fixed there is only a tblwarnings table now. and f8 through the code and all is well. I hit the run button, and it doesnt take any time at all, almost seemingly as if nothing is going on..

and in the tblWarnings table there is nothing in there.. there should be a few errors though....

again thank you!!! I cant even believe the support on this board!
Oct 24 '07 #11

nico5038
Expert 2.5K+
P: 3,072
OK, let's see where it goes wrong.
The F8 is really needed multiple times to step line by line through the code.
Don't use the Run (F5) as it will make the progress "invisible" as it executes all code instantly.
Place the textcursor on a command and press F8 to see the yellow line work through the code. When you hover the mousepointer above a field the value will show, check or it's the value you expect.
Especially check or the tablenames are found, else nothing will happen...

Nic;o)
Oct 24 '07 #12

100+
P: 164
I can press the f8 key 7 times. then it kicks back up to
If Left(td.Name, 8) = "terminfo" Then

then
end if
next


I can hover the codes but it doesn't make too much sense to me..

Its getting so close to being done too!
Oct 24 '07 #13

nico5038
Expert 2.5K+
P: 3,072
Looks to me no table with "terminfo" as the first 8 characters is found.
Hover above the td.name to see which tablename is "active" and check the "terminfo" to be exactly correct.

Nic;o)
Oct 24 '07 #14

100+
P: 164
td.Name="~TMPCLP340661"
???

that is when I hover over td.Name

My tables are linked tables to a txt file, not sure if that has anything to do with it
Oct 24 '07 #15

nico5038
Expert 2.5K+
P: 3,072
These are temporary tables, you'll also run into MSys.. tables, just ignore them.
The IF is created to skip these.

Nic;o)
Oct 24 '07 #16

100+
P: 164
So all seems as if it correct? I dont get why it isnt working
Oct 24 '07 #17

100+
P: 164
I posted this db on the net

http://www.mykesdesigns.com/tblrock1/access/terminfo1.accdb
Oct 24 '07 #18

nico5038
Expert 2.5K+
P: 3,072
Strange, looks like the tablename is Case sensitive, try:

If Left(td.Name, 8) = "Terminfo" Then

Can't test more as the linked tables aren't present...

Nic;o)
Oct 24 '07 #19

100+
P: 164
I believe I tried that, and it gave me a runtime error.. I am not at work anymore, and those files were on a network drive. Ill try it again tomorrow, and If it doesnt work ill post a few of those files for you.

thanks you for everything!
Oct 24 '07 #20

nico5038
Expert 2.5K+
P: 3,072
When you get an error, please post here the line, the errornumber and message.

Nic;o)
Oct 25 '07 #21

100+
P: 164
Hey nico,

alright. I am all about screencaps, as you can tell.

Here is the error after changing to a capitol T.

http://www.mykesdesigns.com/tblrock1/access/module4.jpg

and If I hit debug, it takes me here.

http://www.mykesdesigns.com/tblrock1/access/module5.jpg


--------------------

here is a few terminfos if it would help

http://www.mykesdesigns.com/tblrock1/access/terminfo22.txt
http://www.mykesdesigns.com/tblrock1/access/terminfo24.txt
http://www.mykesdesigns.com/tblrock1/access/terminfo25.txt

Hopefully we can get this licked today!

When you get an error, please post here the line, the errornumber and message.

Nic;o)
Oct 25 '07 #22

nico5038
Expert 2.5K+
P: 3,072
Looks like the linked tables are causing trouble.
Just try to open one from the database window and see or that does work, otherwise relink them.

Nic;o)
Oct 25 '07 #23

100+
P: 164
I did both of those, and it is still giving me the same thing!! arggg

Looks like the linked tables are causing trouble.
Just try to open one from the database window and see or that does work, otherwise relink them.

Nic;o)
Oct 25 '07 #24

100+
P: 164
Okay, so I am dinking around here trying out some lame things.. I think I may have found a really cheap way around this....

I have got it so that I have about 60 queries... each query is an office. the query returns the info that I need it to..

the query has, StorNum, TermName, Role, LastUpd

they work fine indidually.. how would I combine this all into ONE master query???
Oct 25 '07 #25

nico5038
Expert 2.5K+
P: 3,072
Assuming the queries are named "qryA2, "qryB", etc. use:
Expand|Select|Wrap|Line Numbers
  1. select * from qryA
  2. UNION ALL
  3. select * from qryB
  4. UNION ALL
  5. select * from qryC
  6. UNION ALL
  7. ...
  8.  
Nic;o)
Oct 25 '07 #26

100+
P: 164
Great I will try this out tomorrow morning!

Assuming the queries are named "qryA2, "qryB", etc. use:
Expand|Select|Wrap|Line Numbers
  1. select * from qryA
  2. UNION ALL
  3. select * from qryB
  4. UNION ALL
  5. select * from qryC
  6. UNION ALL
  7. ...
  8.  
Nic;o)
Oct 25 '07 #27

100+
P: 164
Okay, so My queries are simply called

22
23
24
etc


so i typed in

SELECT * FROM 22
UNION ALL
SELECT * FROM 23
UNION ALL


I get an invalid syntax error, and it wont save,

Am I typing this in wrong??? I am in SQL view of a new query.
Oct 26 '07 #28

nico5038
Expert 2.5K+
P: 3,072
Better to use for your queries names like qry22, qry23, etc. Thus Access can't mix them with numbers. In your case you would probably have to add [ and] around the numbers to indicate it's a name and no number.

Nic;o)
Oct 26 '07 #29

100+
P: 164
This is what I have in the code

SELECT * FROM qry22
UNION ALL
SELECT * FROM qry23
UNION ALL


it gives me a "Syntax Error, Incomplete Query Clause"

I am missing something..
Oct 26 '07 #30

100+
P: 164
I was missing the forsaken
;


Thank you so much for all your help nico, I think this will workout perfectly now!!!
Oct 26 '07 #31

100+
P: 164
Expand|Select|Wrap|Line Numbers
  1. Select * FROM qry22
  2. UNION ALL
  3. SELECT * FROM qry24
  4. UNION ALL
  5. SELECT * FROM qry25
  6. UNION ALL
  7. SELECT * FROM qry26
  8. UNION ALL
  9. SELECT * FROM qry27
  10. UNION ALL
  11. SELECT * FROM qry31
  12. UNION ALL
  13. SELECT * FROM qry32
  14. UNION ALL
  15. SELECT * FROM qry33
  16. UNION ALL
  17. SELECT * FROM qry34
  18. UNION ALL
  19. SELECT * FROM qry35
  20. UNION ALL
  21. SELECT * FROM qry36
  22. UNION ALL
  23. SELECT * FROM qry51
  24. UNION ALL
  25. SELECT * FROM qry52
  26. UNION ALL
  27. SELECT * FROM qry53
  28. UNION ALL
  29. SELECT * FROM qry54
  30. UNION ALL
  31. SELECT * FROM qry55
  32. UNION ALL
  33. SELECT * FROM qry56
  34. UNION ALL
  35. SELECT * FROM qry57
  36. UNION ALL
  37. SELECT * FROM qry61
  38. UNION ALL
  39. SELECT * FROM qry62
  40. UNION ALL
  41. SELECT * FROM qry63
  42. UNION ALL
  43. SELECT * FROM qry64
  44. UNION ALL
  45. SELECT * FROM qry65
  46. UNION ALL
  47. SELECT * FROM qry66
  48. UNION ALL
  49. SELECT * FROM qry67
  50. UNION ALL
  51. SELECT * FROM qry68
  52. UNION ALL
  53. SELECT * FROM qry69
  54. UNION ALL
  55. SELECT * FROM qry71
  56. UNION ALL
  57. SELECT * FROM qry84
  58. UNION ALL
  59. SELECT * FROM qry85
  60. UNION ALL
  61. SELECT * FROM qry87
  62. UNION ALL
  63. SELECT * FROM qry89
  64. UNION ALL
  65. SELECT * FROM qry91
  66. UNION ALL
  67. SELECT * FROM qry92
  68. UNION ALL
  69. SELECT * FROM qry93
  70. UNION ALL
  71. SELECT * FROM qry94
  72. UNION ALL
  73. SELECT * FROM qry95
  74. UNION ALL
  75. SELECT * FROM qry101
  76. UNION ALL
  77. SELECT * FROM qry102
  78. UNION ALL
  79. SELECT * FROM qry103
  80. UNION ALL
  81. SELECT * FROM qry104
  82. UNION ALL
  83. SELECT * FROM qry105
  84. UNION ALL
  85. SELECT * FROM qry106
  86. UNION ALL
  87. SELECT * FROM qry107
  88. UNION ALL
  89. SELECT * FROM qry108;
  90.  


I need 13 more of these. If I add one more here it gives me

"query too complex" I am using access 2007. any advice???????
Oct 26 '07 #32

nico5038
Expert 2.5K+
P: 3,072
Hmm, just "fool" Access by creating two or three UNION queries separately and then a final UNION with these two or three.

When this doesn't work we'll use these two or three to create Append queries and fill a temp table :-)

Nic;o)
Oct 26 '07 #33

NeoPa
Expert Mod 15k+
P: 31,470
Are your queries of the form :
Expand|Select|Wrap|Line Numbers
  1. SELECT Count((Left([TermName],2) & [Role])='02Backup') AS GoodBUp,
  2.        Count([Role]='Backup') AS AllBUp,
  3.        ([GoodBUp]=[AllBUp] AND [AllBUp]=1) AS Check
  4. FROM [TermInfo101]
It would probably be a good idea to loop through this (as a template changing the table name) for each table in code. If any query returns anything but two ones it is faulty (the data that is).
Oct 27 '07 #34

100+
P: 164
Okay I think I got this all down. I am going to post it all tomorrow morning!
Oct 29 '07 #35

NeoPa
Expert Mod 15k+
P: 31,470
We'll look forward to it :)
Oct 29 '07 #36

100+
P: 164
ALRIGHT, heres what I have... A BIG MESS....

the access db. Works just fine... beautifully, might I add. I may have done some COMPLETELY unnecessary work to get the outcome that I needed... but I got it.

heres what I did... and then Ill tell you my next huge problem.


What I did:

okay so there are about 70 LINKED tables to comma separated files telling the info on these registers.. one of the problems with these files is no where in the file does it say what store it came from.. the filename contains the store EX: terminfo22.

so I needed the store number to be in the final output of this.. so I then created 70 more tables with one text field, and named them "table##"

I then wrote 70 queries that looked like this
----------------------------------------------------------------------------------------------------------------
FIELD Store Number TermName Role LastUpd
TABLE Table71 Terminfo71 Terminfo71 Terminfo71
Criteria Like "02*" "Workstation"
OR Like "03*" "Backup"
OR Like "04*" "Backup"
OR Like "05*" "Backup"

__________________________________________________ ________________

I named these qry##

okay now after all of these were created. I then made TWO queries. because pulling all of the queries into one didnt work.. (no big deal)


So I now have two master queries.

they each pull about half of these "qry##" by doing the

SQL-
SELECT * FROM qry22
UNION ALL
SELECT * FROMqry23;


it all works nicely inside of access.... But then the next part came along.


I needed to get (essentially these two queries to output to a web page)

So I have been using a little bit of Microsoft's visual web developer.. I have gotten situations like this to work before were it links to an access db and pulls a query and thats that, its like data all the time..

well for some reason i could NOT get it to work for this one, I was getting some VERY weird errors...

So i decided to try and do the access 2007 upgrade to sql process and thought Visual Studio will work better with sql vs access..

So after about a 2 hour converting this database to a Microsoft sql express database, I now have all the tables in the database, problem is none of the queries carried over... I thought, OH well Its not going to hurt me to create 70 more it may take me like 2 hrs, oh well...

but I cannot seem how to write the queries I was doing up above there..

I can get this in Microsfts sql quiry bulder...



SELECT [table 69].[Store Number], Terminfo69.TermName, Terminfo69.Role, Terminfo69.LastUpd
FROM [table 69] CROSS JOIN
Terminfo69
WHERE (Terminfo69.Role ='backup') AND (Terminfo69.TermName LIKE '05*')


The underlined part is wrong... How can I get this to give me IF the first two characters start with "05" because what follows after the 5 is always something different.


I really hope this all making sense. NEO and nico!!! I am need of help!!! I am going crazy!
Oct 29 '07 #37

nico5038
Expert 2.5K+
P: 3,072
Hmm, guess we need to return to the Function performing the testing.
The impossibility to load the linked .csv is something that needs to be solved first.

Nic;o)
Oct 29 '07 #38

100+
P: 164
Sorry that went over my head! My brain is fried, I knew nothing of sql going into this project, So id say its going pretty good thanks to the help of you all!

here is what I have now...

I have a bunch of queries... So for instance, how would I join 2 queries like this?

query 1.

Expand|Select|Wrap|Line Numbers
  1. SELECT     [table 69].[Store Number], Terminfo69.TermName, Terminfo69.Role, Terminfo69.LastUpd
  2. FROM         [table 69] CROSS JOIN
  3.                       Terminfo69
  4. WHERE     (Terminfo69.Role = N'Workstation') AND (Terminfo69.TermName LIKE N'02%') OR
  5.                       (Terminfo69.Role = N'Backup') AND (Terminfo69.TermName LIKE N'03%') OR
  6.                       (Terminfo69.Role = N'Backup') AND (Terminfo69.TermName LIKE N'04%') OR
  7.                       (Terminfo69.Role = N'Backup') AND (Terminfo69.TermName LIKE N'05%')

and query 2..

Expand|Select|Wrap|Line Numbers
  1. SELECT     [table 92].[Store Number], Terminfo92.TermName, Terminfo92.Role, Terminfo92.LastUpd
  2. FROM         [table 92] CROSS JOIN
  3.                       Terminfo92
  4. WHERE     (Terminfo92.Role = N'Workstation') AND (Terminfo92.TermName LIKE N'02%') OR
  5.                       (Terminfo92.Role = N'Backup') AND (Terminfo92.TermName LIKE N'03%') OR
  6.                       (Terminfo92.Role = N'Backup') AND (Terminfo92.TermName LIKE N'04%') OR
  7.                       (Terminfo92.Role = N'Backup') AND (Terminfo92.TermName LIKE N'05%')




If I could figure out how to keep adding these into the one query I am pretty sure I will be golden!
Oct 29 '07 #39

100+
P: 164
I am sorry those queries look so bad, I cannot figure out how to make look them nice.
Oct 29 '07 #40

nico5038
Expert 2.5K+
P: 3,072
Take your time and please reread my first comments to see the working of the function.

Nic;o)
Oct 29 '07 #41

100+
P: 164
sorry, It is loading those... the queries that I posted will load them just fine.

now I just need tofigure out how to join multiple queries..

What you gave before the:

Select * FROM "query name"
UNION ALL
etc.... it worked fine in access... however sql server does not like it.
Oct 29 '07 #42

NeoPa
Expert Mod 15k+
P: 31,470
What we seem to have here is a situation where, after panicking a bit, you try all sorts of things one after the other and get into a real mess.
This is pretty well the worst way (though understandable) of trying to tackle a problem. With remote help this is even more destructive.

What you need to do is STOP.
Take stock.
Only ever move on from one idea to another when you're happy the first is resolved. Otherwise what you get is experts trying to follow your hyper-jumps all around the place and getting hopelessly lost.

There are some solid ideas in this thread. There are some that didn't make it.
I suggest you forget all posts from #37 onwards and try to approach this again calmly and one item at a time (A cup of tea might help too ;)).

In fact a resume of the whole thread, carefully presented, may help. We need to look at simplifying the problem before we can come up with answers to try. Does this make sense?
Oct 29 '07 #43

100+
P: 164
Absolutely. I couldn't agree more. Let me REstart this by saying the end result of this needs to be that it needs to be on a web page. So me or two other people can simply go to the page and check on the situation of these registers.

As long as this can be accomplished I do not care what I need to do to get this done.

I apologize for the amateur like hyper jumping. I was trying like crazy to accomplish my problem.

With all of this being said, and my cup of tea gone down nicely, I'll reiterate a couple things just in case I may have confused anyone with my jumping, and not making any sense.

In a set location on one of our servers are about 70 files that get updated about 5 times a day that report whether a stores terminals are on "Backup" or "Workstation". In the text of these files is NO mention of a store number. The File NAME does however contain the store number. The end result needs to be a query containing any store whos "#2" terminal is NOT set as Backup OR any register number besides "#2" that is NOT set as Workstation.

If you need anymore information I'll happily give.

Hopefully we can get this thing down.

-Looking forward to your response.
Oct 29 '07 #44

NeoPa
Expert Mod 15k+
P: 31,470
That seems quite concise :)
Firstly let me say that I have pretty well no experience of developing for the web. Access I can handle, but web stuff I'm no use to you for.
I guess one of your main problems is that Access has a limit of how many record sources it can combine in a UNION query.
Some questions for you :
  1. Currently, how is the data in these files made available to Access?
  2. Does it have multiple linked tables?
  3. How many of them are there in total?
  4. Does that vary at all?
If the structure moves (not static) then we may need to implement some of this in a function instead of a simple (well maybe not simple) query.

PS. For purposes of clarity, it's a good idea to introduce the answer to a question with the matching letter. That way it's clear which answer matches each question and it can be found quickly when scanning through.
Oct 29 '07 #45

100+
P: 164
That seems quite concise :)
Firstly let me say that I have pretty well no experience of developing for the web. Access I can handle, but web stuff I'm no use to you for.
I guess one of your main problems is that Access has a limit of how many record sources it can combine in a UNION query.
Some questions for you :
  1. Currently, how is the data in these files made available to Access?
  2. Does it have multiple linked tables?
  3. How many of them are there in total?
  4. Does that vary at all?
If the structure moves (not static) then we may need to implement some of this in a function instead of a simple (well maybe not simple) query.

PS. For purposes of clarity, it's a good idea to introduce the answer to a question with the matching letter. That way it's clear which answer matches each question and it can be found quickly when scanning through.
A. Right now access is getting this information via Linked table to these files.
B. Yes there are multiple linked tables
C. There are nearly 70, I dont have in front of me right now, but it is about 70-75.
D. This number will never change.

Also do not have to worry about the structure moving. If it did I know how to fix the problem, though.

If there is one thing I can do in this project, it is to get this database on this web page, as long as the info I need is ONE query.

I tried switching over to SQL Server because I reached my goal in getting the information I needed in access. For some reason Visual Studio would not work well with the access database, but it works beautifully with SQL Server.

I could have kept trying to get this to work with access IE: pretty much rebuilding my access database, but the way I was setting it up, there was no way I was doing that again. That took me like an entire DAY. So either I need a real nice clean way to get this to work with access, or I need to use SQL server. And I am down for which ever way.

any more questions, throw em at me!
Oct 30 '07 #46

MMcCarthy
Expert Mod 10K+
P: 14,534
I would forget about the queries and use a temporary table to store your records. Populate it using VBA.

First step is to create a function to get the names of the tables. Assuming there are no other tables in the database the function below should work. Otherwise you would have to check the name of the table. Within that function you would call a second function which would append the data to the table created for this purpose. This way you will be working with one table instead of one or more queries. Some of this code you will find is similar to the code provided earlier by Nico.

Lets call the temporary table tmpTable. You can create it once and you don't have to actually delete it but just delete the records each time you start. My advice would be to use an Access "Make Table" query using one of the linked text files as a basis. This way Access will determine the datatype of the fields. I would advise going in afterwards and checking them out though just in case.

Now to the code.

Expand|Select|Wrap|Line Numbers
  1. Function getData()
  2. Dim  tbl as DAO.Tabledef
  3.  
  4.     DoCmd.SetWarnings False ' turn off Access warnings
  5.  
  6.     DoCmd.RunSQL "DELETE * FROM tmpTable" ' Empty tmpTable
  7.  
  8.     ' this will check each table name so exclude the ones you don't want
  9.     ' it will then call the "appendToTable" function
  10.     For each tbl IN CurrentDb.Tabledefs
  11.         If tbl.Name NOT LIKE "MSys*" AND tbl.Name NOT LIKE "~*" AND tbl.Name NOT LIKE "tmpTable" AND tbl.Name NOT LIKE "tblWarnings" Then
  12.             appendToTable(tbl.Name)
  13.         End If
  14.     Next tbl
  15.  
  16. EndFunction
  17.  
This function will append all the relevant txt Linked tables to a new table

Expand|Select|Wrap|Line Numbers
  1. Function appendToTable(String tblName)
  2. Dim rs As DAO.Recordset
  3.  
  4.     Set rs = CurrentDb.OpenRecordset(tblName)
  5.  
  6.     rs.MoveFirst
  7.     ' this will append each record from the passed table name to the tmpTable
  8.     Do Until rs.EOF
  9.         ' Each field name will have to be specified.  
  10.         ' For the purposes of examples I am just going to use 3 and show
  11.         ' how to pass different value types.  The first is a String, the second 
  12.         ' a date and the third a number.
  13.         DoCmd.RunSQL "INSERT INTO tmpTable (FieldName1, FieldName2, FieldName3) " & _
  14.             "VALUES ('" & rs!FieldName1 & "', #" & rs!FieldName2 & "#, " & rs!FieldName3 & ")"
  15.  
  16.         rs.MoveNext
  17.     Loop
  18.  
  19.     rs.Close
  20.     Set rs = Nothing
  21.  
  22. End Function
  23.  
Oct 30 '07 #47

100+
P: 164
I would forget about the queries and use a temporary table to store your records. Populate it using VBA.

First step is to create a function to get the names of the tables. Assuming there are no other tables in the database the function below should work. Otherwise you would have to check the name of the table. Within that function you would call a second function which would append the data to the table created for this purpose. This way you will be working with one table instead of one or more queries. Some of this code you will find is similar to the code provided earlier by Nico.

Lets call the temporary table tmpTable. You can create it once and you don't have to actually delete it but just delete the records each time you start. My advice would be to use an Access "Make Table" query using one of the linked text files as a basis. This way Access will determine the datatype of the fields. I would advise going in afterwards and checking them out though just in case.

Now to the code.

Expand|Select|Wrap|Line Numbers
  1. Function getData()
  2. Dim  tbl as DAO.Tabledef
  3.  
  4.     DoCmd.SetWarnings False ' turn off Access warnings
  5.  
  6.     DoCmd.RunSQL "DELETE * FROM tmpTable" ' Empty tmpTable
  7.  
  8.     ' this will check each table name so exclude the ones you don't want
  9.     ' it will then call the "appendToTable" function
  10.     For each tbl IN CurrentDb.Tabledefs
  11.         If tbl.Name NOT LIKE "MSys*" AND tbl.Name NOT LIKE "~*" AND tbl.Name NOT LIKE "tmpTable" AND tbl.Name NOT LIKE "tblWarnings" Then
  12.             appendToTable(tbl.Name)
  13.         End If
  14.     Next tbl
  15.  
  16. EndFunction
  17.  
This function will append all the relevant txt Linked tables to a new table

Expand|Select|Wrap|Line Numbers
  1. Function appendToTable(String tblName)
  2. Dim rs As DAO.Recordset
  3.  
  4.     Set rs = CurrentDb.OpenRecordset(tblName)
  5.  
  6.     rs.MoveFirst
  7.     ' this will append each record from the passed table name to the tmpTable
  8.     Do Until rs.EOF
  9.         ' Each field name will have to be specified.  
  10.         ' For the purposes of examples I am just going to use 3 and show
  11.         ' how to pass different value types.  The first is a String, the second 
  12.         ' a date and the third a number.
  13.         DoCmd.RunSQL "INSERT INTO tmpTable (FieldName1, FieldName2, FieldName3) " & _
  14.             "VALUES ('" & rs!FieldName1 & "', #" & rs!FieldName2 & "#, " & rs!FieldName3 & ")"
  15.  
  16.         rs.MoveNext
  17.     Loop
  18.  
  19.     rs.Close
  20.     Set rs = Nothing
  21.  
  22. End Function
  23.  

Thank you so much for your contribution...

I have looked through this a bit and I am super tired and not at work right now, so i can look at it better tomorrow morning.. I dont know much about what you said, but the reason I need this to be in a query is to simply leave this database alone once it is finished. I never want to touch it again. I am going to use an ASP.net ap to pull this query out automatically.

Will this be able to happen with the above mentioned method??? because if so Ill be more than happy to give this a shot!
Oct 30 '07 #48

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank you so much for your contribution...

I have looked through this a bit and I am super tired and not at work right now, so i can look at it better tomorrow morning.. I dont know much about what you said, but the reason I need this to be in a query is to simply leave this database alone once it is finished. I never want to touch it again. I am going to use an ASP.net ap to pull this query out automatically.

Will this be able to happen with the above mentioned method??? because if so Ill be more than happy to give this a shot!
The problem with using code is it has to be triggered.

Triggering the code requires a minimun that the database be opened. You could trigger the code to run as soon as the database is opened.

If you were planning on using ASP to trigger the upload of the query as a recordset after creating a connection to the database then this code could probably be adapted to ASP. All that is really required is a connection to the database but you would have to talk to the ASP experts about adapting the code syntax. I'm happy to go through the logic of whats happening.

Let me know tomorrow if you want me to explain further.

Mary
Oct 30 '07 #49

100+
P: 164
The problem with using code is it has to be triggered.

Triggering the code requires a minimun that the database be opened. You could trigger the code to run as soon as the database is opened.

If you were planning on using ASP to trigger the upload of the query as a recordset after creating a connection to the database then this code could probably be adapted to ASP. All that is really required is a connection to the database but you would have to talk to the ASP experts about adapting the code syntax. I'm happy to go through the logic of whats happening.

Let me know tomorrow if you want me to explain further.

Mary

Yeah I do not know a whole lot about ASP. I know that I have used it before to create a DB connection with access and it pulls a query for me, and it works wonderfully for me, And I figured the query is only thing I could figure out how to get working because it does not require me to open the DB or any of that. Thats why I was hoping to get all of this output into a query.
Oct 30 '07 #50

112 Replies

Post your reply

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