help writing a multiple table query

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

[code=vb]
Function getData()
Dim tbl as DAO.Tabledef

DoCmd.SetWarnin gs 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 "appendToTa ble" function
For each tbl IN CurrentDb.Table defs
If tbl.Name NOT LIKE "MSys*" AND tbl.Name NOT LIKE "~*" AND tbl.Name NOT LIKE "tmpTable" AND tbl.Name NOT LIKE "tblWarning s" Then
appendToTable(t bl.Name)
End If
Next tbl

EndFunction
[/code]

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

[code=vb]
Function appendToTable(S tring tblName)
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenR ecordset(tblNam e)

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
[/code]

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

[code=vb]
Function getData()
Dim tbl as DAO.Tabledef

DoCmd.SetWarnin gs 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 "appendToTa ble" function
For each tbl IN CurrentDb.Table defs
If tbl.Name NOT LIKE "MSys*" AND tbl.Name NOT LIKE "~*" AND tbl.Name NOT LIKE "tmpTable" AND tbl.Name NOT LIKE "tblWarning s" Then
appendToTable(t bl.Name)
End If
Next tbl

EndFunction
[/code]

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

[code=vb]
Function appendToTable(S tring tblName)
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenR ecordset(tblNam e)

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
[/code]

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.

[MMcCarthy]

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.

Pulling a table is the same as pulling a query. The issue will be triggering the creation of the table.

We have some very good experts in ASP. I'll ask a couple of them to look at this issue and see if its feasible.

Mary

[mbatestblrock]

good deal, I am looking forward to what they come up with!

[NeoPa]

How practical will it be to have this merge process run?
Pros :

1. Much (much) easier and efficient processing of the data in your queries.

Cons :

1. Data out of date if files updated in the background.
2. Need to find a way to trigger the preparation of the data without it running every time the data is queried from the web page.

If you're happy with this balance (only you really know the full requiirements of your system), then that's the way to proceed.
Let us know :)

[mbatestblrock]

How practical will it be to have this merge process run?
Pros :

1. Much (much) easier and efficient processing of the data in your queries.

Cons :

1. Data out of date if files updated in the background.
2. Need to find a way to trigger the preparation of the data without it running every time the data is queried from the web page.

If you're happy with this balance (only you really know the full requiirements of your system), then that's the way to proceed.
Let us know :)

Well the data will be changing in these files in the background, constantly.. Whatever it takes to launch the web page, and it does all the work for me. I have done it before. but I was dealing with VBA or anything. just straight up queries, and linked tables, and it worked just fine.

I dont know if this can be accomplished without using VBA, though?

[NeoPa]

Firstly, is the changing data critical?
Is it possible perhaps to run the merge of all the data before EVERY query of the database (seems unlikely)?
I believe that we can arrange for a query to be called from your web page which can simply return a function's value. If we make this function do some work before it returns the value then we should be able to use this technique to get some VBA code to run from your web page.

[mbatestblrock]

Firstly, is the changing data critical?
Is it possible perhaps to run the merge of all the data before EVERY query of the database (seems unlikely)?
I believe that we can arrange for a query to be called from your web page which can simply return a function's value. If we make this function do some work before it returns the value then we should be able to use this technique to get some VBA code to run from your web page.

Sounds awesome. The changing of data is very critical, If one of these is not set the way they should be, a bunch of data could be potentially lost. We check these files a good couple times a day, which is why I am trying to simplify our lives by doing this instead of reading through 70 text files. and Access is not on all of computers, THUS the web page idea spawned.

[MMcCarthy]

Sounds awesome. The changing of data is very critical, If one of these is not set the way they should be, a bunch of data could be potentially lost. We check these files a good couple times a day, which is why I am trying to simplify our lives by doing this instead of reading through 70 text files. and Access is not on all of computers, THUS the web page idea spawned.

To save some on the query load you could amalgamate the text files to one text file using VBA or possibly through ASP. However, it would still have to be triggered every time the data was accessed.

I have to go out for a few hours but will check in when I get back.

Mary

[mbatestblrock]

To save some on the query load you could amalgamate the text files to one text file using VBA or possibly through ASP. However, it would still have to be triggered every time the data was accessed.

I have to go out for a few hours but will check in when I get back.

Mary

But then I run into the problem with the store number, because all the queries I have is where I get the store number from. Unless there is still a way to got those dang store numbers in there??

[MMcCarthy]

But then I run into the problem with the store number, because all the queries I have is where I get the store number from. Unless there is still a way to got those dang store numbers in there??

That wouldn't be a problem.

To amalgamate a text file you have one empty file that you write new records to. You would loop through all the other files and read them in line by line. You can go to specific values on the line and read the value and set conditions on it just the same as any query. You can then add the store number or any any value you like when adding the data the new text file. Essentially as a new field.

What I'm not sure about is if this can all be done is ASP bypassing the database altogether. However, as Ade (NeoPa) said whatever way we do this will have to be triggered every time the data is accessed.

I'm off now but will check in when I get back.

Mary

[mbatestblrock]

That wouldn't be a problem.

To amalgamate a text file you have one empty file that you write new records to. You would loop through all the other files and read them in line by line. You can go to specific values on the line and read the value and set conditions on it just the same as any query. You can then add the store number or any any value you like when adding the data the new text file. Essentially as a new field.

What I'm not sure about is if this can all be done is ASP bypassing the database altogether. However, as Ade (NeoPa) said whatever way we do this will have to be triggered every time the data is accessed.

I'm off now but will check in when I get back.

Mary

So what we need to figure out is how to trigger this vba code in the databse with asp correct?