DataBase linking

I am trying to link two separate databases using ms access. I have one
database that is a sales log inventory, and another that is a customer data
base. I would like to add a command button on my sales log inventory that
would automatically go into the customer database, find the appropriate
customer number using the number given by the sales log inventory, and output
the ship to and bill to address that is located in the customer data base.

Any thoughts would be appreciated

Bryan

re: DataBase linking

Probably the easiest way to go about this would be to create linked
tables to the other database and read/write to them.

The quickest way to link tables is to right click in your database
table window and select "link tables..." - Then select the database
containing the target table and click ok. You will be given a list of
tables in that database, select the table(s) you wish to link to and
click ok.

Now you can use them as if they were in your own database.

re: DataBase linking

Probably the easiest way to go about this would be to create linked
tables to the other database and read/write to them.

The quickest way to link tables is to right click in your database
table window and select "link tables..." - Then select the database
containing the target table and click ok. You will be given a list of
tables in that database, select the table(s) you wish to link to and
click ok.

Now you can use them as if they were in your own database.

re: DataBase linking

Thanks for the info. . .The problem I am having now is that I can't reference
the database in the vb code section. If I use me.** I can only reference
the fields that are in the original database. I need to get a field that is
in the linked database. Again, thank you for your help


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

re: DataBase linking

Thanks for the info. . .The problem I am having now is that I can't reference
the database in the vb code section. If I use me.** I can only reference
the fields that are in the original database. I need to get a field that is
in the linked database. Again, thank you for your help


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

re: DataBase linking

Sorry, to elaborate more. I have a form in which I am using the database
for the sales log. In that form, I am trying to figure out how to access the
linked data that I have. Sorry if I am unable to elaborate more, as I am
very novice to access.

Bryan wrote:[color=blue]
>Thanks for the info. . .The problem I am having now is that I can't reference
>the database in the vb code section. If I use me.** I can only reference
>the fields that are in the original database. I need to get a field that is
>in the linked database. Again, thank you for your help[/color]


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

re: DataBase linking

Sorry, to elaborate more. I have a form in which I am using the database
for the sales log. In that form, I am trying to figure out how to access the
linked data that I have. Sorry if I am unable to elaborate more, as I am
very novice to access.

Bryan wrote:[color=blue]
>Thanks for the info. . .The problem I am having now is that I can't reference
>the database in the vb code section. If I use me.** I can only reference
>the fields that are in the original database. I need to get a field that is
>in the linked database. Again, thank you for your help[/color]


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

re: DataBase linking

Yes, linked tables are probably the best solution. But you can also
look up values from an external database in code:

Function ShipAddress(lngCustomerID As Long) As String
Dim DB As Database
Dim rst As Recordset
Set DB = DBEngine(0).OpenDatabase("C:\CustomerDatabase.mdb" )
Set rst = DB.OpenRecordset("tblCustomers", dbOpenDynaset)
With rst
.FindFirst ("[CustomerID]=" & lngCustomerID)
If Not .NoMatch() Then ShipAddress = .Fields("ShipAddress")
.Close
End With
DB.Close
Set rst = Nothing
Set DB = Nothing
End Function

re: DataBase linking

Yes, linked tables are probably the best solution. But you can also
look up values from an external database in code:

Function ShipAddress(lngCustomerID As Long) As String
Dim DB As Database
Dim rst As Recordset
Set DB = DBEngine(0).OpenDatabase("C:\CustomerDatabase.mdb" )
Set rst = DB.OpenRecordset("tblCustomers", dbOpenDynaset)
With rst
.FindFirst ("[CustomerID]=" & lngCustomerID)
If Not .NoMatch() Then ShipAddress = .Fields("ShipAddress")
.Close
End With
DB.Close
Set rst = Nothing
Set DB = Nothing
End Function

re: DataBase linking

If you are using a form, simply bind the form's recordset to the linked
table, and you should have access to those fields with Me.Whatever.

The other way would be:
Dim rst as Recordset
Set rst = CurrentDB.OpenRecordset("LinkedTableName")

Now you reference any field with rst.Fields("FieldName").
Just remember to do this when you're done (i like to encourage good
programming practice):
rst.Close
Set rst = Nothing

The final method, completely independent of linked tables, I posted a
function for before.

re: DataBase linking

If you are using a form, simply bind the form's recordset to the linked
table, and you should have access to those fields with Me.Whatever.

The other way would be:
Dim rst as Recordset
Set rst = CurrentDB.OpenRecordset("LinkedTableName")

Now you reference any field with rst.Fields("FieldName").
Just remember to do this when you're done (i like to encourage good
programming practice):
rst.Close
Set rst = Nothing

The final method, completely independent of linked tables, I posted a
function for before.

re: DataBase linking

Private Sub ShipmentRequest_Click()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("customerdbnew")

This is how i started the new sub.. . .except it is giving me a type mismatch
error. Thanks again for all the help!

Bryan


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

re: DataBase linking

Private Sub ShipmentRequest_Click()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("customerdbnew")

This is how i started the new sub.. . .except it is giving me a type mismatch
error. Thanks again for all the help!

Bryan


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

re: DataBase linking

guess you'll have to figure that one out on your own, as I can't
possibly know what code line is causing the error (just debug it).
Don't forget, in your ShipmentRequest method you must still use
..FindFirst to jump to the right customer record.

re: DataBase linking

"the chiller" <timedilation@gmail.com> wrote in message
news:1123267210.951534.310010@g43g2000cwa.googlegr oups.com...[color=blue]
> If you are using a form, simply bind the form's recordset to the linked
> table, and you should have access to those fields with Me.Whatever.
>
> The other way would be:
> Dim rst as Recordset
> Set rst = CurrentDB.OpenRecordset("LinkedTableName")
>
> Now you reference any field with rst.Fields("FieldName").
> Just remember to do this when you're done (i like to encourage good
> programming practice):
> rst.Close
> Set rst = Nothing
>
> The final method, completely independent of linked tables, I posted a
> function for before.[/color]


(i like to encourage good programming practice):
Then why not write:
Dim rst As DAO.Recordset
or
Dim rst As ADODB.Recordset
so there is no ambiguity as to whether you are using the DAO or ADO object
model?

re: DataBase linking

guess you'll have to figure that one out on your own, as I can't
possibly know what code line is causing the error (just debug it).
Don't forget, in your ShipmentRequest method you must still use
..FindFirst to jump to the right customer record.

re: DataBase linking

"the chiller" <timedilation@gmail.com> wrote in message
news:1123267210.951534.310010@g43g2000cwa.googlegr oups.com...[color=blue]
> If you are using a form, simply bind the form's recordset to the linked
> table, and you should have access to those fields with Me.Whatever.
>
> The other way would be:
> Dim rst as Recordset
> Set rst = CurrentDB.OpenRecordset("LinkedTableName")
>
> Now you reference any field with rst.Fields("FieldName").
> Just remember to do this when you're done (i like to encourage good
> programming practice):
> rst.Close
> Set rst = Nothing
>
> The final method, completely independent of linked tables, I posted a
> function for before.[/color]


(i like to encourage good programming practice):
Then why not write:
Dim rst As DAO.Recordset
or
Dim rst As ADODB.Recordset
so there is no ambiguity as to whether you are using the DAO or ADO object
model?

re: DataBase linking

Thank you.
I figured the first part out, i had to had the ms DAO 3.6 library, and then
use dim dao.recordset

thanks again
bryan

the chiller wrote:[color=blue]
>guess you'll have to figure that one out on your own, as I can't
>possibly know what code line is causing the error (just debug it).
>Don't forget, in your ShipmentRequest method you must still use
>.FindFirst to jump to the right customer record.[/color]


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1

re: DataBase linking

"Bryan via AccessMonster.com" <forum@AccessMonster.com> wrote in message
news:5266B6B9ADAB4@AccessMonster.com...[color=blue]
> Private Sub ShipmentRequest_Click()
> Dim rst As Recordset
> Set rst = CurrentDb.OpenRecordset("customerdbnew")
>
> This is how i started the new sub.. . .except it is giving me a type
> mismatch
> error. Thanks again for all the help!
>
> Bryan[/color]

When viewing the code, select Tools>References and uncheck:
Microsoft ActiveX Data Objects 2.1 Library
and add a reference to:
Microsoft DAO 3.6 Object Library

Then change the code to
Dim rst As DAO.Recordset
make sure it copiles first (Debug>Compile)