DataBase linking

[Bryan via AccessMonster.com]

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

[BillCo]

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.

[BillCo]

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.

[Bryan via AccessMonster.com]

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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200508/1

[Bryan via AccessMonster.com]

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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200508/1

[Bryan via AccessMonster.com]

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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200508/1

[Bryan via AccessMonster.com]

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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200508/1

[the chiller]

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(lng CustomerID As Long) As String
Dim DB As Database
Dim rst As Recordset
Set DB = DBEngine(0).Ope nDatabase("C:\C ustomerDatabase .mdb")
Set rst = DB.OpenRecordse t("tblCustomers ", dbOpenDynaset)
With rst
.FindFirst ("[CustomerID]=" & lngCustomerID)
If Not .NoMatch() Then ShipAddress = .Fields("ShipAd dress")
.Close
End With
DB.Close
Set rst = Nothing
Set DB = Nothing
End Function

[the chiller]

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(lng CustomerID As Long) As String
Dim DB As Database
Dim rst As Recordset
Set DB = DBEngine(0).Ope nDatabase("C:\C ustomerDatabase .mdb")
Set rst = DB.OpenRecordse t("tblCustomers ", dbOpenDynaset)
With rst
.FindFirst ("[CustomerID]=" & lngCustomerID)
If Not .NoMatch() Then ShipAddress = .Fields("ShipAd dress")
.Close
End With
DB.Close
Set rst = Nothing
Set DB = Nothing
End Function

[the chiller]

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.OpenR ecordset("Linke dTableName")

Now you reference any field with rst.Fields("Fie ldName").
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.

[the chiller]

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.OpenR ecordset("Linke dTableName")

Now you reference any field with rst.Fields("Fie ldName").
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.

[Bryan via AccessMonster.com]

Re: DataBase linking

Private Sub ShipmentRequest _Click()
Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset("custo merdbnew")

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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200508/1

[Bryan via AccessMonster.com]

Re: DataBase linking

Private Sub ShipmentRequest _Click()
Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset("custo merdbnew")

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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200508/1

[the chiller]

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.

[Justin Hoffman]

Re: DataBase linking


"the chiller" <timedilation@g mail.com> wrote in message
news:1123267210 .951534.310010@ g43g2000cwa.goo glegroups.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.OpenR ecordset("Linke dTableName")
>
> Now you reference any field with rst.Fields("Fie ldName").
> 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?