VBA Creating Table in Second MDB returning curious error

  • Thread starter Thread starter Dkline
  • Start date Start date
D

Dkline

I have a VBA module that transfer 19 queries as tables to another MDB.

I'm using a Private Sub called 19 times:
DoCmd.TransferDatabase acExport, "Microsoft Access", PathToSource,
acTable, NameOfQuery, NameOfTable, False, False

The offending call is:
strNameOfQuery = "qry_subform program_value by inv account"
strNameOfTable = "tbl_value by investment acct"
ExportTables strPathToSourceDB, strNameOfQuery, strNameOfTable

On this one only of the 19 I get the error message:
" Runtime error '3090'. Resultant table not allowed to have more than one
Autonumber field."

I find this curious since I'm not creating or including an Autonumber field.
The Select query itself is a combination of two other queries and a table.

What should I be looking for in the underlying queries and tables in this
query that would cause this error?
 
Look at the query that is causing the problem.

Check the fields that you have selected for exporting to
the new Table. You will probably find that you have
selected more than one Autonumber field to be exported in
the Query.

Check the Queries and Table in the source query to ensure
that only one, or no, Autonumber field is exported. If
you are have selected all of the fields from the Table and
Queries in this Query and there are Autonumber Fields in
more than one of the Source Objects, then this is the
problem.

HTH


Tony C.
 
This is a bogus solution since senior management needs both fields in this
table. I created an expression:

InvestmentAccountID: [Investment Strategy].[InvestmentAccountID]*1

Is there a better, cleaner way of doing this? A union doesn't help - I still
have two primary keys which are autonumbered. Exporting them from a query to
a table - they still retain their property of being an AutoNumber field. Any
way to defeat that?

Is there a ore elegant and reliable solution?
 
What's wrong with the solution you came up with?

This is a bogus solution since senior management needs both fields in this
table. I created an expression:

InvestmentAccountID: [Investment Strategy].[InvestmentAccountID]*1

Is there a better, cleaner way of doing this? A union doesn't help - I still
have two primary keys which are autonumbered. Exporting them from a query to
a table - they still retain their property of being an AutoNumber field. Any
way to defeat that?

Is there a ore elegant and reliable solution?

Tony C said:
Look at the query that is causing the problem.

Check the fields that you have selected for exporting to
the new Table. You will probably find that you have
selected more than one Autonumber field to be exported in
the Query.

Check the Queries and Table in the source query to ensure
that only one, or no, Autonumber field is exported. If
you are have selected all of the fields from the Table and
Queries in this Query and there are Autonumber Fields in
more than one of the Source Objects, then this is the
problem.

HTH


Tony C.
Click to expand...
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
I just feel that someday it's going to come back and get me at some point.
Somewhere, sometime at the worst possible it will go wrong. and create a
table with bad data.

Just doesn't seem bullet-proof.

Peter R. Fletcher said:
What's wrong with the solution you came up with?

This is a bogus solution since senior management needs both fields in this
table. I created an expression:

InvestmentAccountID: [Investment Strategy].[InvestmentAccountID]*1

Is there a better, cleaner way of doing this? A union doesn't help - I still
have two primary keys which are autonumbered. Exporting them from a query to
a table - they still retain their property of being an AutoNumber field. Any
way to defeat that?

Is there a ore elegant and reliable solution?

Tony C said:
Look at the query that is causing the problem.

Check the fields that you have selected for exporting to
the new Table. You will probably find that you have
selected more than one Autonumber field to be exported in
the Query.

Check the Queries and Table in the source query to ensure
that only one, or no, Autonumber field is exported. If
you are have selected all of the fields from the Table and
Queries in this Query and there are Autonumber Fields in
more than one of the Source Objects, then this is the
problem.

HTH


Tony C.
-----Original Message-----
I have a VBA module that transfer 19 queries as tables to
another MDB.

I'm using a Private Sub called 19 times:
DoCmd.TransferDatabase acExport, "Microsoft Access",
PathToSource,
acTable, NameOfQuery, NameOfTable, False, False

The offending call is:
strNameOfQuery = "qry_subform program_value by inv
account"
strNameOfTable = "tbl_value by investment acct"
ExportTables strPathToSourceDB, strNameOfQuery,
strNameOfTable

On this one only of the 19 I get the error message:
" Runtime error '3090'. Resultant table not allowed to
have more than one
Autonumber field."

I find this curious since I'm not creating or including
an Autonumber field.
The Select query itself is a combination of two other
queries and a table.

What should I be looking for in the underlying queries
and tables in this
query that would cause this error?


.
Click to expand...
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
Click to expand...
=---
 
I can't think of any circumstances under which your workaround should
fail, nor any better approach to the problem.

I just feel that someday it's going to come back and get me at some point.
Somewhere, sometime at the worst possible it will go wrong. and create a
table with bad data.

Just doesn't seem bullet-proof.

Peter R. Fletcher said:
What's wrong with the solution you came up with?

This is a bogus solution since senior management needs both fields in this
table. I created an expression:

InvestmentAccountID: [Investment Strategy].[InvestmentAccountID]*1

Is there a better, cleaner way of doing this? A union doesn't help - I still
have two primary keys which are autonumbered. Exporting them from a query to
a table - they still retain their property of being an AutoNumber field. Any
way to defeat that?

Is there a ore elegant and reliable solution?

Look at the query that is causing the problem.

Check the fields that you have selected for exporting to
the new Table. You will probably find that you have
selected more than one Autonumber field to be exported in
the Query.

Check the Queries and Table in the source query to ensure
that only one, or no, Autonumber field is exported. If
you are have selected all of the fields from the Table and
Queries in this Query and there are Autonumber Fields in
more than one of the Source Objects, then this is the
problem.

HTH


Tony C.
-----Original Message-----
I have a VBA module that transfer 19 queries as tables to
another MDB.

I'm using a Private Sub called 19 times:
DoCmd.TransferDatabase acExport, "Microsoft Access",
PathToSource,
acTable, NameOfQuery, NameOfTable, False, False

The offending call is:
strNameOfQuery = "qry_subform program_value by inv
account"
strNameOfTable = "tbl_value by investment acct"
ExportTables strPathToSourceDB, strNameOfQuery,
strNameOfTable

On this one only of the 19 I get the error message:
" Runtime error '3090'. Resultant table not allowed to
have more than one
Autonumber field."

I find this curious since I'm not creating or including
an Autonumber field.
The Select query itself is a combination of two other
queries and a table.

What should I be looking for in the underlying queries
and tables in this
query that would cause this error?


.
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
Click to expand...
=---
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top