help writing a multiple table query

[mbatestblrock]

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!

[nico5038]

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)

[mbatestblrock]

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!

[nico5038]

No need, the function isn't that hard.

function fncFindBackup()
' these dim's will need you to set a reference to the "Microsoft DAO version 3.##" library
' and remove the "Microsoft Active Data Objects" library when selected.
' use Tools/References to (un)set them
dim td as DAO.Tabledef
dim rs as DAO.Recordset
' The results are stored in a temp table tblWarnings
' Create that table with one 250 char text field "Warning"
dim rsF as DAO.recordset 
 
' initialize the warnings
currentdb.execute ("delete * from tblWarning")
 
set rsF = currentdb.openrecordset("tblWarnings")
 
' Process all tables
for each td in currentdb.tabledefs
   ' check the name to "fit"
   if left(td.name,8) = "terminfo" then
      ' get all records for 'Backup' role
      set rs = currentdb.openrecordset(select * from " & td.name & " where Role ='Backup'")
          if rs.eof and rs.bof then
             ' Nothing found
             rsF.addnew
             rsF!Warnings = "Table: " & td.name & " has no backup"
             rsF.Update
          else
             ' move to end for getting correct count
             rs.movelast
             ' test one record
             if rs.recordcount > 1 then
                rsF.addnew
                rsF!Warnings = "Table: " & td.name & " has " & rs.recordcount & " backups"
                rsF.Update
              else
                 'one record found, now test "02=" in start of [Terminal Info] field
                 if left(rs![Terminal Info],3) <> "02=" then
                   rsF.addnew
                   rsF!Warnings = "Table: " & td.name & " has no backup in '02='"
                   rsF.Update
                 endif 
             endif   
          endif
   endif
next
 
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)

[mbatestblrock]

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.

function fncFindBackup()
' these dim's will need you to set a reference to the "Microsoft DAO version 3.##" library
' and remove the "Microsoft Active Data Objects" library when selected.
' use Tools/References to (un)set them
dim td as DAO.Tabledef
dim rs as DAO.Recordset
' The results are stored in a temp table tblWarnings
' Create that table with one 250 char text field "Warning"
dim rsF as DAO.recordset 
 
' initialize the warnings
currentdb.execute ("delete * from tblWarning")
 
set rsF = currentdb.openrecordset("tblWarnings")
 
' Process all tables
for each td in currentdb.tabledefs
   ' check the name to "fit"
   if left(td.name,8) = "terminfo" then
      ' get all records for 'Backup' role
      set rs = currentdb.openrecordset(select * from " & td.name & " where Role ='Backup'")
          if rs.eof and rs.bof then
             ' Nothing found
             rsF.addnew
             rsF!Warnings = "Table: " & td.name & " has no backup"
             rsF.Update
          else
             ' move to end for getting correct count
             rs.movelast
             ' test one record
             if rs.recordcount > 1 then
                rsF.addnew
                rsF!Warnings = "Table: " & td.name & " has " & rs.recordcount & " backups"
                rsF.Update
              else
                 'one record found, now test "02=" in start of [Terminal Info] field
                 if left(rs![Terminal Info],3) <> "02=" then
                   rsF.addnew
                   rsF!Warnings = "Table: " & td.name & " has no backup in '02='"
                   rsF.Update
                 endif 
             endif   
          endif
   endif
next
 
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)

[nico5038]

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)

[mbatestblrock]

I hope I am doing all of this correctly!


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

[nico5038]

Sorry, forgot a " before the SELECT, try:

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

Nic;o)

[mbatestblrock]

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!

[nico5038]

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)

[mbatestblrock]

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!

[nico5038]

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)

[mbatestblrock]

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!

[nico5038]

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)

[mbatestblrock]

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

[nico5038]

These are temporary tables, you'll also run into MSys.. tables, just ignore them.
The IF is created to skip these.

Nic;o)

[mbatestblrock]

So all seems as if it correct? I dont get why it isnt working

[mbatestblrock]

I posted this db on the net

http://www.mykesdesigns.com/tblrock1/access/terminfo1.accdb

[nico5038]

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)

[mbatestblrock]

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!

[nico5038]

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

Nic;o)

[mbatestblrock]

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)

[nico5038]

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)

[mbatestblrock]

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)

[mbatestblrock]

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???

[nico5038]

Assuming the queries are named "qryA2, "qryB", etc. use:

select * from qryA
UNION ALL
select * from qryB
UNION ALL
select * from qryC
UNION ALL
...
 

Nic;o)

[mbatestblrock]

Great I will try this out tomorrow morning!

Assuming the queries are named "qryA2, "qryB", etc. use:

select * from qryA
UNION ALL
select * from qryB
UNION ALL
select * from qryC
UNION ALL
...
 

Nic;o)

[mbatestblrock]

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.

[nico5038]

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)

[mbatestblrock]

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..

[mbatestblrock]

I was missing the forsaken
;


Thank you so much for all your help nico, I think this will workout perfectly now!!!

[mbatestblrock]

Select * FROM qry22
UNION ALL
SELECT * FROM qry24
UNION ALL
SELECT * FROM qry25
UNION ALL
SELECT * FROM qry26
UNION ALL
SELECT * FROM qry27
UNION ALL
SELECT * FROM qry31
UNION ALL
SELECT * FROM qry32
UNION ALL
SELECT * FROM qry33
UNION ALL
SELECT * FROM qry34
UNION ALL
SELECT * FROM qry35
UNION ALL
SELECT * FROM qry36
UNION ALL
SELECT * FROM qry51
UNION ALL
SELECT * FROM qry52
UNION ALL
SELECT * FROM qry53
UNION ALL
SELECT * FROM qry54
UNION ALL
SELECT * FROM qry55
UNION ALL
SELECT * FROM qry56
UNION ALL
SELECT * FROM qry57
UNION ALL
SELECT * FROM qry61
UNION ALL
SELECT * FROM qry62
UNION ALL
SELECT * FROM qry63
UNION ALL
SELECT * FROM qry64
UNION ALL
SELECT * FROM qry65
UNION ALL
SELECT * FROM qry66
UNION ALL
SELECT * FROM qry67
UNION ALL
SELECT * FROM qry68
UNION ALL
SELECT * FROM qry69
UNION ALL
SELECT * FROM qry71
UNION ALL
SELECT * FROM qry84
UNION ALL
SELECT * FROM qry85
UNION ALL
SELECT * FROM qry87
UNION ALL
SELECT * FROM qry89
UNION ALL
SELECT * FROM qry91
UNION ALL
SELECT * FROM qry92
UNION ALL
SELECT * FROM qry93
UNION ALL
SELECT * FROM qry94
UNION ALL
SELECT * FROM qry95
UNION ALL
SELECT * FROM qry101
UNION ALL
SELECT * FROM qry102
UNION ALL
SELECT * FROM qry103
UNION ALL
SELECT * FROM qry104
UNION ALL
SELECT * FROM qry105
UNION ALL
SELECT * FROM qry106
UNION ALL
SELECT * FROM qry107
UNION ALL
SELECT * FROM qry108;
 

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???????

[nico5038]

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)

[NeoPa]

Are your queries of the form :

SELECT Count((Left([TermName],2) & [Role])='02Backup') AS GoodBUp,
       Count([Role]='Backup') AS AllBUp,
       ([GoodBUp]=[AllBUp] AND [AllBUp]=1) AS Check
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).

[mbatestblrock]

Okay I think I got this all down. I am going to post it all tomorrow morning!

[NeoPa]

We'll look forward to it :)

[mbatestblrock]

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!

[nico5038]

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)

[mbatestblrock]

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.

SELECT     [table 69].[Store Number], Terminfo69.TermName, Terminfo69.Role, Terminfo69.LastUpd
FROM         [table 69] CROSS JOIN
                      Terminfo69
WHERE     (Terminfo69.Role = N'Workstation') AND (Terminfo69.TermName LIKE N'02%') OR
                      (Terminfo69.Role = N'Backup') AND (Terminfo69.TermName LIKE N'03%') OR
                      (Terminfo69.Role = N'Backup') AND (Terminfo69.TermName LIKE N'04%') OR
                      (Terminfo69.Role = N'Backup') AND (Terminfo69.TermName LIKE N'05%')

and query 2..

SELECT     [table 92].[Store Number], Terminfo92.TermName, Terminfo92.Role, Terminfo92.LastUpd
FROM         [table 92] CROSS JOIN
                      Terminfo92
WHERE     (Terminfo92.Role = N'Workstation') AND (Terminfo92.TermName LIKE N'02%') OR
                      (Terminfo92.Role = N'Backup') AND (Terminfo92.TermName LIKE N'03%') OR
                      (Terminfo92.Role = N'Backup') AND (Terminfo92.TermName LIKE N'04%') OR
                      (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!

[mbatestblrock]

I am sorry those queries look so bad, I cannot figure out how to make look them nice.

[nico5038]

Take your time and please reread my first comments to see the working of the function.

Nic;o)

[mbatestblrock]

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.

[NeoPa]

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?

[mbatestblrock]

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.

[NeoPa]

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.

[mbatestblrock]

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!

[MMcCarthy]

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.

Function getData()
Dim  tbl as DAO.Tabledef
 
    DoCmd.SetWarnings False ' turn off Access warnings
 
    DoCmd.RunSQL "DELETE * FROM tmpTable" ' Empty tmpTable
 
    ' this will check each table name so exclude the ones you don't want
    ' it will then call the "appendToTable" function
    For each tbl IN CurrentDb.Tabledefs
        If tbl.Name NOT LIKE "MSys*" AND tbl.Name NOT LIKE "~*" AND tbl.Name NOT LIKE "tmpTable" AND tbl.Name NOT LIKE "tblWarnings" Then
            appendToTable(tbl.Name)
        End If
    Next tbl
 
EndFunction
 

This function will append all the relevant txt Linked tables to a new table

Function appendToTable(String tblName)
Dim rs As DAO.Recordset
 
    Set rs = CurrentDb.OpenRecordset(tblName)
 
    rs.MoveFirst
    ' this will append each record from the passed table name to the tmpTable
    Do Until rs.EOF
        ' Each field name will have to be specified.  
        ' For the purposes of examples I am just going to use 3 and show
        ' how to pass different value types.  The first is a String, the second 
        ' a date and the third a number.
        DoCmd.RunSQL "INSERT INTO tmpTable (FieldName1, FieldName2, FieldName3) " & _
            "VALUES ('" & rs!FieldName1 & "', #" & rs!FieldName2 & "#, " & rs!FieldName3 & ")"
 
        rs.MoveNext
    Loop
 
    rs.Close
    Set rs = Nothing
 
End Function
 

[mbatestblrock]

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.

Function getData()
Dim  tbl as DAO.Tabledef
 
    DoCmd.SetWarnings False ' turn off Access warnings
 
    DoCmd.RunSQL "DELETE * FROM tmpTable" ' Empty tmpTable
 
    ' this will check each table name so exclude the ones you don't want
    ' it will then call the "appendToTable" function
    For each tbl IN CurrentDb.Tabledefs
        If tbl.Name NOT LIKE "MSys*" AND tbl.Name NOT LIKE "~*" AND tbl.Name NOT LIKE "tmpTable" AND tbl.Name NOT LIKE "tblWarnings" Then
            appendToTable(tbl.Name)
        End If
    Next tbl
 
EndFunction
 

This function will append all the relevant txt Linked tables to a new table

Function appendToTable(String tblName)
Dim rs As DAO.Recordset
 
    Set rs = CurrentDb.OpenRecordset(tblName)
 
    rs.MoveFirst
    ' this will append each record from the passed table name to the tmpTable
    Do Until rs.EOF
        ' Each field name will have to be specified.  
        ' For the purposes of examples I am just going to use 3 and show
        ' how to pass different value types.  The first is a String, the second 
        ' a date and the third a number.
        DoCmd.RunSQL "INSERT INTO tmpTable (FieldName1, FieldName2, FieldName3) " & _
            "VALUES ('" & rs!FieldName1 & "', #" & rs!FieldName2 & "#, " & rs!FieldName3 & ")"
 
        rs.MoveNext
    Loop
 
    rs.Close
    Set rs = Nothing
 
End Function
 

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!

[MMcCarthy]

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

[mbatestblrock]

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.