union causing too many fields defined

[Guest]

Hello Everyone,

I am not a Access expert so please keep that in mind.

Here is the situation. For my job I am building a database to store/sort
seals that have been tested. The number of when one is tested is 135
variables which means that there is 135 fields. The variables are the same
each time and in the same order. There are 1000s of seals and dozens of
spreadsheets. What I need to do is put all the spreadsheets(tables) into one
place and be able to search it with a form. I have made the form and it
works and I was using a union querry to put all the data together.

Problem
Now that I have put them together with the union querry I get the too many
fields defined error. The code I use is union all select *From [fieldname].
Is there away to put them all together and not get this error or must I make
100s of smaller union querries all link together.

any help would be appreciated.

Note The number of variable(fields) is set and cannot not be smaller. They
all come together on a Excel spreadsheet and it cannot be changed in anyway.

 

[Guest]

Typically #error occurs only when you have a function and have not considered
things like null values. It does not sounds like you are using any functions
(are you?). So I would expect the union to be really slow, but not to cause
errors. Is it possible that any of your tables have different names for the
fields, because union likes the fields to be named the same and in the same
order. I would also look at how null is being used. Also union queries will
remove duplicates assuming that there is a primary key on the table.

Sorry that I cannot provide a definte answer, but these are the places that
I would look.

Is there any way that you could create a new table that has the same
structure as all of these tables and then possibly run some code that would
dump the records from each table into it? I believe this would give you the
same as the union query does, but it would be a lot more manageable in one
table. Obviously every time you make a change to the underlying data, you
would need to recreate this table.

Please let me know if I can provide more assistance.

 

[Guest]

I made a second link to the exact same Excel worksheet but named the linked
table differently. This makes it so I know I have everything in the same
order and amount and I get the to many fields defined error.

I am not sure by what you mean by functions. Could you please explain.

Where and how would I write the code to dump them together because that is
what I thought I was doing with the union code.

thank you
swordsman8

Typically #error occurs only when you have a function and have not considered
things like null values. It does not sounds like you are using any functions
(are you?). So I would expect the union to be really slow, but not to cause
errors. Is it possible that any of your tables have different names for the
fields, because union likes the fields to be named the same and in the same
order. I would also look at how null is being used. Also union queries will
remove duplicates assuming that there is a primary key on the table.

Sorry that I cannot provide a definte answer, but these are the places that
I would look.

Is there any way that you could create a new table that has the same
structure as all of these tables and then possibly run some code that would
dump the records from each table into it? I believe this would give you the
same as the union query does, but it would be a lot more manageable in one
table. Obviously every time you make a change to the underlying data, you
would need to recreate this table.

Please let me know if I can provide more assistance.

Hello Everyone,

I am not a Access expert so please keep that in mind.

Here is the situation. For my job I am building a database to store/sort
seals that have been tested. The number of when one is tested is 135
variables which means that there is 135 fields. The variables are the same
each time and in the same order. There are 1000s of seals and dozens of
spreadsheets. What I need to do is put all the spreadsheets(tables) into one
place and be able to search it with a form. I have made the form and it
works and I was using a union querry to put all the data together.

Problem
Now that I have put them together with the union querry I get the too many
fields defined error. The code I use is union all select *From [fieldname].
Is there away to put them all together and not get this error or must I make
100s of smaller union querries all link together.

any help would be appreciated.

Note The number of variable(fields) is set and cannot not be smaller. They
all come together on a Excel spreadsheet and it cannot be changed in anyway.

 

[Guest]

A union query is still a query, unless you are appending all of the data into
one table. A hardcoded table would include all of the data from all of your
spreadsheets (another copy), and may be easier to use.

This code assumes that all of the linked tables in your database have the
same structure (or at least the field names all exist in another table called
NewTable). Here is some quick and dirty code that will move all of the data
from all of the linked tables into this new table.

Sub CopyAllTablesToOneTable()
Dim tbl As TableDef
Dim strQuery As String
Dim qry As QueryDef
'this should call the new table where the data will reside
Const MainTable As String = "NewTable"

'clear out the main table before starting
Set qry = CurrentDb.CreateQueryDef("", "delete * from [" & MainTable &
"]")
qry.Execute

For Each tbl In CurrentDb.TableDefs
'this will only look at linked tables (assuming that all linked
tables are ones with similar info
If Len(tbl.Connect) > 0 Then
If tbl.Name <> MainTable Then
strQuery = "INSERT INTO [" & MainTable & "] SELECT a.* FROM
[" & tbl.Name & "] AS a;"
qry.SQL = strQuery
qry.Execute
End If
End If
Next

Set tbl = Nothing
Set qry = Nothing
End Sub

Please let me know if you need any more assistance.

I made a second link to the exact same Excel worksheet but named the linked
table differently. This makes it so I know I have everything in the same
order and amount and I get the to many fields defined error.

I am not sure by what you mean by functions. Could you please explain.

Where and how would I write the code to dump them together because that is
what I thought I was doing with the union code.

thank you
swordsman8

Typically #error occurs only when you have a function and have not considered
things like null values. It does not sounds like you are using any functions
(are you?). So I would expect the union to be really slow, but not to cause
errors. Is it possible that any of your tables have different names for the
fields, because union likes the fields to be named the same and in the same
order. I would also look at how null is being used. Also union queries will
remove duplicates assuming that there is a primary key on the table.

Sorry that I cannot provide a definte answer, but these are the places that
I would look.

Is there any way that you could create a new table that has the same
structure as all of these tables and then possibly run some code that would
dump the records from each table into it? I believe this would give you the
same as the union query does, but it would be a lot more manageable in one
table. Obviously every time you make a change to the underlying data, you
would need to recreate this table.

Please let me know if I can provide more assistance.

Hello Everyone,

I am not a Access expert so please keep that in mind.

Here is the situation. For my job I am building a database to store/sort
seals that have been tested. The number of when one is tested is 135
variables which means that there is 135 fields. The variables are the same
each time and in the same order. There are 1000s of seals and dozens of
spreadsheets. What I need to do is put all the spreadsheets(tables) into one
place and be able to search it with a form. I have made the form and it
works and I was using a union querry to put all the data together.

Problem
Now that I have put them together with the union querry I get the too many
fields defined error. The code I use is union all select *From [fieldname].
Is there away to put them all together and not get this error or must I make
100s of smaller union querries all link together.

any help would be appreciated.

Note The number of variable(fields) is set and cannot not be smaller. They
all come together on a Excel spreadsheet and it cannot be changed in anyway.

 

[Guest]

I am looking at the code I believe I understand it and the logic.

The problem is where do I place the code so that it will work? Do I need to
make a table for it all to go in to before the code will work?

Thank you for all of the help
Swordsman8

A union query is still a query, unless you are appending all of the data into
one table. A hardcoded table would include all of the data from all of your
spreadsheets (another copy), and may be easier to use.

This code assumes that all of the linked tables in your database have the
same structure (or at least the field names all exist in another table called
NewTable). Here is some quick and dirty code that will move all of the data
from all of the linked tables into this new table.

Sub CopyAllTablesToOneTable()
Dim tbl As TableDef
Dim strQuery As String
Dim qry As QueryDef
'this should call the new table where the data will reside
Const MainTable As String = "NewTable"

'clear out the main table before starting
Set qry = CurrentDb.CreateQueryDef("", "delete * from [" & MainTable &
"]")
qry.Execute

For Each tbl In CurrentDb.TableDefs
'this will only look at linked tables (assuming that all linked
tables are ones with similar info
If Len(tbl.Connect) > 0 Then
If tbl.Name <> MainTable Then
strQuery = "INSERT INTO [" & MainTable & "] SELECT a.* FROM
[" & tbl.Name & "] AS a;"
qry.SQL = strQuery
qry.Execute
End If
End If
Next

Set tbl = Nothing
Set qry = Nothing
End Sub

Please let me know if you need any more assistance.

I made a second link to the exact same Excel worksheet but named the linked
table differently. This makes it so I know I have everything in the same
order and amount and I get the to many fields defined error.

I am not sure by what you mean by functions. Could you please explain.

Where and how would I write the code to dump them together because that is
what I thought I was doing with the union code.

thank you
swordsman8

Typically #error occurs only when you have a function and have not considered
things like null values. It does not sounds like you are using any functions
(are you?). So I would expect the union to be really slow, but not to cause
errors. Is it possible that any of your tables have different names for the
fields, because union likes the fields to be named the same and in the same
order. I would also look at how null is being used. Also union queries will
remove duplicates assuming that there is a primary key on the table.

Sorry that I cannot provide a definte answer, but these are the places that
I would look.

Is there any way that you could create a new table that has the same
structure as all of these tables and then possibly run some code that would
dump the records from each table into it? I believe this would give you the
same as the union query does, but it would be a lot more manageable in one
table. Obviously every time you make a change to the underlying data, you
would need to recreate this table.

Please let me know if I can provide more assistance.

:

Hello Everyone,

I am not a Access expert so please keep that in mind.

Here is the situation. For my job I am building a database to store/sort
seals that have been tested. The number of when one is tested is 135
variables which means that there is 135 fields. The variables are the same
each time and in the same order. There are 1000s of seals and dozens of
spreadsheets. What I need to do is put all the spreadsheets(tables) into one
place and be able to search it with a form. I have made the form and it
works and I was using a union querry to put all the data together.

Problem
Now that I have put them together with the union querry I get the too many
fields defined error. The code I use is union all select *From [fieldname].
Is there away to put them all together and not get this error or must I make
100s of smaller union querries all link together.

any help would be appreciated.

Note The number of variable(fields) is set and cannot not be smaller. They
all come together on a Excel spreadsheet and it cannot be changed in anyway.

 

[Guest]

Yes, create a table that all of the data will go in. Enter this code into a
new vba module, and just press play to run.

Please let me know if I can provide more assistance.

I am looking at the code I believe I understand it and the logic.

The problem is where do I place the code so that it will work? Do I need to
make a table for it all to go in to before the code will work?

Thank you for all of the help
Swordsman8

A union query is still a query, unless you are appending all of the data into
one table. A hardcoded table would include all of the data from all of your
spreadsheets (another copy), and may be easier to use.

This code assumes that all of the linked tables in your database have the
same structure (or at least the field names all exist in another table called
NewTable). Here is some quick and dirty code that will move all of the data
from all of the linked tables into this new table.

Sub CopyAllTablesToOneTable()
Dim tbl As TableDef
Dim strQuery As String
Dim qry As QueryDef
'this should call the new table where the data will reside
Const MainTable As String = "NewTable"

'clear out the main table before starting
Set qry = CurrentDb.CreateQueryDef("", "delete * from [" & MainTable &
"]")
qry.Execute

For Each tbl In CurrentDb.TableDefs
'this will only look at linked tables (assuming that all linked
tables are ones with similar info
If Len(tbl.Connect) > 0 Then
If tbl.Name <> MainTable Then
strQuery = "INSERT INTO [" & MainTable & "] SELECT a.* FROM
[" & tbl.Name & "] AS a;"
qry.SQL = strQuery
qry.Execute
End If
End If
Next

Set tbl = Nothing
Set qry = Nothing
End Sub

Please let me know if you need any more assistance.

I made a second link to the exact same Excel worksheet but named the linked
table differently. This makes it so I know I have everything in the same
order and amount and I get the to many fields defined error.

I am not sure by what you mean by functions. Could you please explain.

Where and how would I write the code to dump them together because that is
what I thought I was doing with the union code.

thank you
swordsman8


:

Typically #error occurs only when you have a function and have not considered
things like null values. It does not sounds like you are using any functions
(are you?). So I would expect the union to be really slow, but not to cause
errors. Is it possible that any of your tables have different names for the
fields, because union likes the fields to be named the same and in the same
order. I would also look at how null is being used. Also union queries will
remove duplicates assuming that there is a primary key on the table.

Sorry that I cannot provide a definte answer, but these are the places that
I would look.

Is there any way that you could create a new table that has the same
structure as all of these tables and then possibly run some code that would
dump the records from each table into it? I believe this would give you the
same as the union query does, but it would be a lot more manageable in one
table. Obviously every time you make a change to the underlying data, you
would need to recreate this table.

Please let me know if I can provide more assistance.

:

Hello Everyone,

I am not a Access expert so please keep that in mind.

Here is the situation. For my job I am building a database to store/sort
seals that have been tested. The number of when one is tested is 135
variables which means that there is 135 fields. The variables are the same
each time and in the same order. There are 1000s of seals and dozens of
spreadsheets. What I need to do is put all the spreadsheets(tables) into one
place and be able to search it with a form. I have made the form and it
works and I was using a union querry to put all the data together.

Problem
Now that I have put them together with the union querry I get the too many
fields defined error. The code I use is union all select *From [fieldname].
Is there away to put them all together and not get this error or must I make
100s of smaller union querries all link together.

any help would be appreciated.

Note The number of variable(fields) is set and cannot not be smaller. They
all come together on a Excel spreadsheet and it cannot be changed in anyway.

 

[Guest]

Hello Again,

I have the code working and it does a great job but I have a new problem.

I copied an older version of my database and then I renamed it. The NEW
Database is the one that I did the work in such as this code. My problem is
that the code is looking for linked tables in the OLD/original Database. How
do I make it so the New Database is not somehow linked to the OLD one. Also
could you tell me why it is even doing this in the first place.

Thank you,
Swordsman8

Yes, create a table that all of the data will go in. Enter this code into a
new vba module, and just press play to run.

Please let me know if I can provide more assistance.

I am looking at the code I believe I understand it and the logic.

The problem is where do I place the code so that it will work? Do I need to
make a table for it all to go in to before the code will work?

Thank you for all of the help
Swordsman8

A union query is still a query, unless you are appending all of the data into
one table. A hardcoded table would include all of the data from all of your
spreadsheets (another copy), and may be easier to use.

This code assumes that all of the linked tables in your database have the
same structure (or at least the field names all exist in another table called
NewTable). Here is some quick and dirty code that will move all of the data
from all of the linked tables into this new table.

Sub CopyAllTablesToOneTable()
Dim tbl As TableDef
Dim strQuery As String
Dim qry As QueryDef
'this should call the new table where the data will reside
Const MainTable As String = "NewTable"

'clear out the main table before starting
Set qry = CurrentDb.CreateQueryDef("", "delete * from [" & MainTable &
"]")
qry.Execute

For Each tbl In CurrentDb.TableDefs
'this will only look at linked tables (assuming that all linked
tables are ones with similar info
If Len(tbl.Connect) > 0 Then
If tbl.Name <> MainTable Then
strQuery = "INSERT INTO [" & MainTable & "] SELECT a.* FROM
[" & tbl.Name & "] AS a;"
qry.SQL = strQuery
qry.Execute
End If
End If
Next

Set tbl = Nothing
Set qry = Nothing
End Sub

Please let me know if you need any more assistance.

:

I made a second link to the exact same Excel worksheet but named the linked
table differently. This makes it so I know I have everything in the same
order and amount and I get the to many fields defined error.

I am not sure by what you mean by functions. Could you please explain.

Where and how would I write the code to dump them together because that is
what I thought I was doing with the union code.

thank you
swordsman8


:

Typically #error occurs only when you have a function and have not considered
things like null values. It does not sounds like you are using any functions
(are you?). So I would expect the union to be really slow, but not to cause
errors. Is it possible that any of your tables have different names for the
fields, because union likes the fields to be named the same and in the same
order. I would also look at how null is being used. Also union queries will
remove duplicates assuming that there is a primary key on the table.

Sorry that I cannot provide a definte answer, but these are the places that
I would look.

Is there any way that you could create a new table that has the same
structure as all of these tables and then possibly run some code that would
dump the records from each table into it? I believe this would give you the
same as the union query does, but it would be a lot more manageable in one
table. Obviously every time you make a change to the underlying data, you
would need to recreate this table.

Please let me know if I can provide more assistance.

:

Hello Everyone,

I am not a Access expert so please keep that in mind.

Here is the situation. For my job I am building a database to store/sort
seals that have been tested. The number of when one is tested is 135
variables which means that there is 135 fields. The variables are the same
each time and in the same order. There are 1000s of seals and dozens of
spreadsheets. What I need to do is put all the spreadsheets(tables) into one
place and be able to search it with a form. I have made the form and it
works and I was using a union querry to put all the data together.

Problem
Now that I have put them together with the union querry I get the too many
fields defined error. The code I use is union all select *From [fieldname].
Is there away to put them all together and not get this error or must I make
100s of smaller union querries all link together.

any help would be appreciated.

Note The number of variable(fields) is set and cannot not be smaller. They
all come together on a Excel spreadsheet and it cannot be changed in anyway.