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.
>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
[quoted text clipped - 5 lines]
>
> Thanks.
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
[quoted text clipped - 5 lines]
>
> Thanks.
Graham R Seach - 29 Sep 2005 07:20 GMT
<<...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
---------------------------
> 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
[quoted text clipped - 27 lines]
>>
>> Thanks.
Robert Morley - 29 Sep 2005 15:20 GMT
Ummm...that's what I said. :)
> <<...create a view in SQL Server..."
> That's true, but you'll need to use 3-part naming:
[quoted text clipped - 37 lines]
>>>
>>> Thanks.
Graham R Seach - 30 Sep 2005 05:33 GMT
Sorry Robert; I didn't see that bit. :-)
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
> Ummm...that's what I said. :)
>
[quoted text clipped - 39 lines]
>>>>
>>>> Thanks.