Updating Tables in Other Databases through Forms

  • Thread starter Thread starter tbd
  • Start date Start date
T

tbd

Can anyone help? I would like to create a form in one
database that refers to a table in another one. Is this
possible?

Basically at the minute I have one DB that contains around
15 Tables and approx. 60 queries. This DB does a lot of
calculating and manipulation of Data to provide me with
six queries that I export and pick up in a second DB. The
reason I do this is because I am querying the Data by
Form, and it was very slow doing this in the first DB
because of the volume of tables/queries etc.

I also don't want users to have access to the first DB,
despite the fact they will be using the Data created in
it. This is fine apart from the fact I need to be able to
delete and add records to ONE table in the "Base" DB at
regular intervals. Is it possible therefore to set up a
form in my second DB which will allow me to manipulate the
Data in the table of the other DB. I can't simply move
the table into the new DB as it is linked to all of the
calculations and manipulations I am doing the obtain my
final six queries. Can anyone help?

Thanks in advance!
 
Hi.
This DB does a lot of
calculating and manipulation of Data to provide me with
six queries that I export and pick up in a second DB.
Click to expand...

Why not just query the data in the first database (your "Base" database)
from the second database? You wouldn't need to worry about importing or
maintaining the same queries in two different databases. For example, if
the first database was named Base.MDB, located in the C:\Sales directory,
and it had a table named tblInvoices, you could create a query in the second
database with the following syntax:

SELECT *
FROM tblInvoices IN '' [;DATABASE=C:\Sales\Base.MDB;];
Is it possible therefore to set up a
form in my second DB which will allow me to manipulate the
Data in the table of the other DB.
Click to expand...

If you save and name this query, then you can update your remote table,
tblInvoices, in the form by making this query the record source for the
form. You'll be able to add and delete records from this form, too.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Thanks for your help on this gunny! I think it will work
perfectly, however, the SQL Statement does not seem to be
working. When I try to view the query I get the following
error message:

The Microsoft Jet Engine can not open the file ''. It is
already opened exlusively by another user, or you need
permission to view its data.

The code i have enter is as follows:

SELECT *
FROM [Options List] IN ' '[;DATABASE=\\Szgb06
\mini\Product\Tom_Hannah\OptionsTakeup.MDB;];

Any idea what I am doing wrong? I have looked at security
permissions in the base DB and I can't see anything that
would prevent me querying the data.

Thanks once again!


-----Original Message-----
Hi.
This DB does a lot of
calculating and manipulation of Data to provide me with
six queries that I export and pick up in a second DB.
Click to expand...

Why not just query the data in the first database (your "Base" database)
from the second database? You wouldn't need to worry about importing or
maintaining the same queries in two different databases. For example, if
the first database was named Base.MDB, located in the C:\Sales directory,
and it had a table named tblInvoices, you could create a query in the second
database with the following syntax:

SELECT *
FROM tblInvoices IN '' [;DATABASE=C:\Sales\Base.MDB;];
Is it possible therefore to set up a
form in my second DB which will allow me to manipulate the
Data in the table of the other DB.
Click to expand...

If you save and name this query, then you can update your remote table,
tblInvoices, in the form by making this query the record source for the
form. You'll be able to add and delete records from this form, too.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


Can anyone help? I would like to create a form in one
database that refers to a table in another one. Is this
possible?

Basically at the minute I have one DB that contains around
15 Tables and approx. 60 queries. This DB does a lot of
calculating and manipulation of Data to provide me with
six queries that I export and pick up in a second DB. The
reason I do this is because I am querying the Data by
Form, and it was very slow doing this in the first DB
because of the volume of tables/queries etc.

I also don't want users to have access to the first DB,
despite the fact they will be using the Data created in
it. This is fine apart from the fact I need to be able to
delete and add records to ONE table in the "Base" DB at
regular intervals. Is it possible therefore to set up a
form in my second DB which will allow me to manipulate the
Data in the table of the other DB. I can't simply move
the table into the new DB as it is linked to all of the
calculations and manipulations I am doing the obtain my
final six queries. Can anyone help?

Thanks in advance!
Click to expand...


.
Click to expand...
 
Hi.

Looks like a syntax error to me. Now, this is really hard to see in the
syntax that I gave to you, but there should not be a space between the two
single quotes after the keyword IN. Also, there should be a space between
the second single quote and the beginning bracket that's just before the
semicolon and the word DATABASE.

Post back if you still have problems.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


tbd said:
Thanks for your help on this gunny! I think it will work
perfectly, however, the SQL Statement does not seem to be
working. When I try to view the query I get the following
error message:

The Microsoft Jet Engine can not open the file ''. It is
already opened exlusively by another user, or you need
permission to view its data.

The code i have enter is as follows:

SELECT *
FROM [Options List] IN ' '[;DATABASE=\\Szgb06
\mini\Product\Tom_Hannah\OptionsTakeup.MDB;];

Any idea what I am doing wrong? I have looked at security
permissions in the base DB and I can't see anything that
would prevent me querying the data.

Thanks once again!


-----Original Message-----
Hi.
This DB does a lot of
calculating and manipulation of Data to provide me with
six queries that I export and pick up in a second DB.
Click to expand...

Why not just query the data in the first database (your "Base" database)
from the second database? You wouldn't need to worry about importing or
maintaining the same queries in two different databases. For example, if
the first database was named Base.MDB, located in the C:\Sales directory,
and it had a table named tblInvoices, you could create a query in the second
database with the following syntax:

SELECT *
FROM tblInvoices IN '' [;DATABASE=C:\Sales\Base.MDB;];
Is it possible therefore to set up a
form in my second DB which will allow me to manipulate the
Data in the table of the other DB.
Click to expand...

If you save and name this query, then you can update your remote table,
tblInvoices, in the form by making this query the record source for the
form. You'll be able to add and delete records from this form, too.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


Can anyone help? I would like to create a form in one
database that refers to a table in another one. Is this
possible?

Basically at the minute I have one DB that contains around
15 Tables and approx. 60 queries. This DB does a lot of
calculating and manipulation of Data to provide me with
six queries that I export and pick up in a second DB. The
reason I do this is because I am querying the Data by
Form, and it was very slow doing this in the first DB
because of the volume of tables/queries etc.

I also don't want users to have access to the first DB,
despite the fact they will be using the Data created in
it. This is fine apart from the fact I need to be able to
delete and add records to ONE table in the "Base" DB at
regular intervals. Is it possible therefore to set up a
form in my second DB which will allow me to manipulate the
Data in the table of the other DB. I can't simply move
the table into the new DB as it is linked to all of the
calculations and manipulations I am doing the obtain my
final six queries. Can anyone help?

Thanks in advance!
Click to expand...


.
Click to expand...
Click to expand...
 
Brilliant, thats it now! Thanks very much for your help!!

TBD

-----Original Message-----
Hi.

Looks like a syntax error to me. Now, this is really hard to see in the
syntax that I gave to you, but there should not be a space between the two
single quotes after the keyword IN. Also, there should be a space between
the second single quote and the beginning bracket that's just before the
semicolon and the word DATABASE.

Post back if you still have problems.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


Thanks for your help on this gunny! I think it will work
perfectly, however, the SQL Statement does not seem to be
working. When I try to view the query I get the following
error message:

The Microsoft Jet Engine can not open the file ''. It is
already opened exlusively by another user, or you need
permission to view its data.

The code i have enter is as follows:

SELECT *
FROM [Options List] IN ' '[;DATABASE=\\Szgb06
\mini\Product\Tom_Hannah\OptionsTakeup.MDB;];

Any idea what I am doing wrong? I have looked at security
permissions in the base DB and I can't see anything that
would prevent me querying the data.

Thanks once again!


-----Original Message-----
Hi.

This DB does a lot of
calculating and manipulation of Data to provide me with
six queries that I export and pick up in a second DB.

Why not just query the data in the first database (your "Base" database)
from the second database? You wouldn't need to worry about importing or
maintaining the same queries in two different
Click to expand...
databases.
For example, if
the first database was named Base.MDB, located in the C:\Sales directory,
and it had a table named tblInvoices, you could create
Click to expand...
a
query in the second
database with the following syntax:

SELECT *
FROM tblInvoices IN '' [;DATABASE=C:\Sales\Base.MDB;];

Is it possible therefore to set up a
form in my second DB which will allow me to
Click to expand...
manipulate
the
Data in the table of the other DB.

If you save and name this query, then you can update
Click to expand...
your
remote table,
tblInvoices, in the form by making this query the
Click to expand...
record
source for the
form. You'll be able to add and delete records from
Click to expand...
this
form, too.
HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address,
Click to expand...
so
that a message
will be forwarded to me.)


Can anyone help? I would like to create a form in one
database that refers to a table in another one. Is this
possible?

Basically at the minute I have one DB that contains around
15 Tables and approx. 60 queries. This DB does a lot of
calculating and manipulation of Data to provide me with
six queries that I export and pick up in a second DB. The
reason I do this is because I am querying the Data by
Form, and it was very slow doing this in the first DB
because of the volume of tables/queries etc.

I also don't want users to have access to the first DB,
despite the fact they will be using the Data created in
it. This is fine apart from the fact I need to be
Click to expand...
able
to
delete and add records to ONE table in the "Base" DB at
regular intervals. Is it possible therefore to set up a
form in my second DB which will allow me to
Click to expand...
manipulate
the
Data in the table of the other DB. I can't simply move
the table into the new DB as it is linked to all of the
calculations and manipulations I am doing the obtain my
final six queries. Can anyone help?

Thanks in advance!


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


.
Click to expand...
 
You're welcome! Glad it helped.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


tbd said:
Brilliant, thats it now! Thanks very much for your help!!

TBD

-----Original Message-----
Hi.

Looks like a syntax error to me. Now, this is really hard to see in the
syntax that I gave to you, but there should not be a space between the two
single quotes after the keyword IN. Also, there should be a space between
the second single quote and the beginning bracket that's just before the
semicolon and the word DATABASE.

Post back if you still have problems.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


Thanks for your help on this gunny! I think it will work
perfectly, however, the SQL Statement does not seem to be
working. When I try to view the query I get the following
error message:

The Microsoft Jet Engine can not open the file ''. It is
already opened exlusively by another user, or you need
permission to view its data.

The code i have enter is as follows:

SELECT *
FROM [Options List] IN ' '[;DATABASE=\\Szgb06
\mini\Product\Tom_Hannah\OptionsTakeup.MDB;];

Any idea what I am doing wrong? I have looked at security
permissions in the base DB and I can't see anything that
would prevent me querying the data.

Thanks once again!



-----Original Message-----
Hi.

This DB does a lot of
calculating and manipulation of Data to provide me with
six queries that I export and pick up in a second DB.

Why not just query the data in the first database
(your "Base" database)
from the second database? You wouldn't need to worry
about importing or
maintaining the same queries in two different databases.
For example, if
the first database was named Base.MDB, located in the
C:\Sales directory,
and it had a table named tblInvoices, you could create a
query in the second
database with the following syntax:

SELECT *
FROM tblInvoices IN '' [;DATABASE=C:\Sales\Base.MDB;];

Is it possible therefore to set up a
form in my second DB which will allow me to manipulate
the
Data in the table of the other DB.

If you save and name this query, then you can update your
remote table,
tblInvoices, in the form by making this query the record
source for the
form. You'll be able to add and delete records from this
form, too.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access
tips.

(Please remove ZERO_SPAM from my reply E-mail address, so
that a message
will be forwarded to me.)


message
Can anyone help? I would like to create a form in one
database that refers to a table in another one. Is this
possible?

Basically at the minute I have one DB that contains
around
15 Tables and approx. 60 queries. This DB does a lot of
calculating and manipulation of Data to provide me with
six queries that I export and pick up in a second DB.
The
reason I do this is because I am querying the Data by
Form, and it was very slow doing this in the first DB
because of the volume of tables/queries etc.

I also don't want users to have access to the first DB,
despite the fact they will be using the Data created in
it. This is fine apart from the fact I need to be able
to
delete and add records to ONE table in the "Base" DB at
regular intervals. Is it possible therefore to set up a
form in my second DB which will allow me to manipulate
the
Data in the table of the other DB. I can't simply move
the table into the new DB as it is linked to all of the
calculations and manipulations I am doing the obtain my
final six queries. Can anyone help?

Thanks in advance!


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top