Connecting to another DB

  • Thread starter Thread starter Andy G
  • Start date Start date
A

Andy G

I have an .adp connected to a SQL database named SCSdb. I just added one
form to the application and would like to set the record source of a drop
down to a table in another database in SQL named SCSAdmin. Can someone
point me in the right direction. I would just like to be able to link a few
tables to the SCSAdmin database but since I'm already connected to SCSdb I
don't believe I can (according to MS Access Developer's Guide to SQL
Server).

Thanks.
 
The simplest way is to write some ADO code (ADODB.Connection/RecordSet) to
get data from the other database. You only need to give the Connection
object a correct ConnectionString.
 
If the other database is on the same server, you can create a view in SQL
Server to access the data, then use that view as your data source in the
form. I don't *think* you can use the alternate database directly in the
form, but someone please correct me if I'm wrong.

In any event, it's very easy to create the view, just prepend the name of
the database to the usual owner.table[.field] syntax...in your case, it
would look like this:

SELECT * FROM SCSAdmin.dbo.MyTable

....or if you wanted to mix & match fields and databases in a join...

SELECT SCSAdmin.dbo.MyTable.MyField, dbo.TableFromSCSdb.OtherField FROM
SCSAdmin.dbo.MyTable INNER JOIN dbo.TableFromSCSdb ON ...

.... you get the idea. You *can* specify SCSdb in front of anything from the
local database for clarity if you want, but I suspect the query designer
would strip them out.



Rob
 
<<...create a view in SQL Server..."
That's true, but you'll need to use 3-part naming:
SELECT * FROM Northwind.dbo.Categories

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Robert Morley said:
If the other database is on the same server, you can create a view in SQL
Server to access the data, then use that view as your data source in the
form. I don't *think* you can use the alternate database directly in the
form, but someone please correct me if I'm wrong.

In any event, it's very easy to create the view, just prepend the name of
the database to the usual owner.table[.field] syntax...in your case, it
would look like this:

SELECT * FROM SCSAdmin.dbo.MyTable

...or if you wanted to mix & match fields and databases in a join...

SELECT SCSAdmin.dbo.MyTable.MyField, dbo.TableFromSCSdb.OtherField FROM
SCSAdmin.dbo.MyTable INNER JOIN dbo.TableFromSCSdb ON ...

... you get the idea. You *can* specify SCSdb in front of anything from
the local database for clarity if you want, but I suspect the query
designer would strip them out.



Rob

Andy G said:
I have an .adp connected to a SQL database named SCSdb. I just added one
form to the application and would like to set the record source of a drop
down to a table in another database in SQL named SCSAdmin. Can someone
point me in the right direction. I would just like to be able to link a
few tables to the SCSAdmin database but since I'm already connected to
SCSdb I don't believe I can (according to MS Access Developer's Guide to
SQL Server).

Thanks.
Click to expand...
Click to expand...
 
Ummm...that's what I said. :)


Graham R Seach said:
<<...create a view in SQL Server..."
That's true, but you'll need to use 3-part naming:
SELECT * FROM Northwind.dbo.Categories

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Robert Morley said:
If the other database is on the same server, you can create a view in SQL
Server to access the data, then use that view as your data source in the
form. I don't *think* you can use the alternate database directly in the
form, but someone please correct me if I'm wrong.

In any event, it's very easy to create the view, just prepend the name of
the database to the usual owner.table[.field] syntax...in your case, it
would look like this:

SELECT * FROM SCSAdmin.dbo.MyTable

...or if you wanted to mix & match fields and databases in a join...

SELECT SCSAdmin.dbo.MyTable.MyField, dbo.TableFromSCSdb.OtherField
FROM SCSAdmin.dbo.MyTable INNER JOIN dbo.TableFromSCSdb ON ...

... you get the idea. You *can* specify SCSdb in front of anything from
the local database for clarity if you want, but I suspect the query
designer would strip them out.



Rob

Andy G said:
I have an .adp connected to a SQL database named SCSdb. I just added one
form to the application and would like to set the record source of a drop
down to a table in another database in SQL named SCSAdmin. Can someone
point me in the right direction. I would just like to be able to link a
few tables to the SCSAdmin database but since I'm already connected to
SCSdb I don't believe I can (according to MS Access Developer's Guide to
SQL Server).

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Sorry Robert; I didn't see that bit. :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Robert Morley said:
Ummm...that's what I said. :)


Graham R Seach said:
<<...create a view in SQL Server..."
That's true, but you'll need to use 3-part naming:
SELECT * FROM Northwind.dbo.Categories

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Robert Morley said:
If the other database is on the same server, you can create a view in
SQL Server to access the data, then use that view as your data source in
the form. I don't *think* you can use the alternate database directly
in the form, but someone please correct me if I'm wrong.

In any event, it's very easy to create the view, just prepend the name
of the database to the usual owner.table[.field] syntax...in your case,
it would look like this:

SELECT * FROM SCSAdmin.dbo.MyTable

...or if you wanted to mix & match fields and databases in a join...

SELECT SCSAdmin.dbo.MyTable.MyField, dbo.TableFromSCSdb.OtherField
FROM SCSAdmin.dbo.MyTable INNER JOIN dbo.TableFromSCSdb ON ...

... you get the idea. You *can* specify SCSdb in front of anything from
the local database for clarity if you want, but I suspect the query
designer would strip them out.



Rob

I have an .adp connected to a SQL database named SCSdb. I just added
one form to the application and would like to set the record source of a
drop down to a table in another database in SQL named SCSAdmin. Can
someone point me in the right direction. I would just like to be able
to link a few tables to the SCSAdmin database but since I'm already
connected to SCSdb I don't believe I can (according to MS Access
Developer's Guide to SQL Server).

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