By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,504 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

DataBase linking

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

P: n/a
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
Nov 13 '05 #5

P: n/a
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:
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
Nov 13 '05 #6

P: n/a
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:
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
Nov 13 '05 #7

P: n/a
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

Nov 13 '05 #8

P: n/a
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

Nov 13 '05 #9

P: n/a
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.

Nov 13 '05 #10

P: n/a
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.

Nov 13 '05 #11

P: n/a
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
Nov 13 '05 #12

P: n/a
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
Nov 13 '05 #13

P: n/a
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.

Nov 13 '05 #14

P: n/a

"the chiller" <ti**********@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
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.

(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?
Nov 13 '05 #15

P: n/a
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.

Nov 13 '05 #16

P: n/a

"the chiller" <ti**********@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
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.

(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?
Nov 13 '05 #17

P: n/a
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:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200508/1
Nov 13 '05 #18

P: n/a
"Bryan via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:52***********@AccessMonster.com...
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


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)
Nov 13 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.