473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
Nov 13 '05 #1
18 1707
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
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
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
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
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
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
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
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
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
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
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
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
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

"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
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

"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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Shyam Singh | last post by:
I have two database Student and Course both have a relation on it. Student ID is the primary key for Student Table and Course ID is the primary key for course table. As per my logic, one student...
5
by: Brian | last post by:
I need to import data from 720 csv files into an Access database so I can do some editing prior to loading into a SQL Server. These files came from data output from a mainframe on a monthly basis....
0
by: gasturbtec | last post by:
please help im new at access programming and i just got this project dropped in my lap because the old programmer quit. i've been doing ok so far but now i need to add code to an existing database...
1
by: deiopajw | last post by:
I have a Back end database on a network drive. The copies of the front end are located on individual pc's (in their C drive). The problem arises when a laptop user naturally hooks up to the...
1
by: gopinathanr | last post by:
I am having a VB 6.0 program where I am using an MS Access database. This database has some tables that have been created by linking to MySql database using the MySQL ODBC 3.51 Driver. When I try...
1
by: gopinathanr | last post by:
I am having a VB 6.0 program where I am using an MS Access database. This database has some tables that have been created by linking to MySql database using the MySQL ODBC 3.51 Driver. When I try...
8
by: rdemyan via AccessMonster.com | last post by:
I've converted my application from A2K format to A2003 format. I tried to follow Allen Browne's protocol in getting my app into A2003 (although I was unable to find informtion on the conversion...
1
by: GregZ | last post by:
I have an Access 97 database that I have uploaded to the internet. I have created a DSN for it with the hosting company and can now access it using .asp code. Everything here works fine. But, I...
7
by: coolsti | last post by:
I have the task to set up an application at work,using MS Access as a front end to a MySQL database. This will be done using an appropriate ODBC driver, and linking the MySQL database to Access. ...
25
by: zmickle | last post by:
Excuse my noobness. I am managing an access database that is shared by 4 users. Management does not want to use any technologies outside of access for this application (no SQL Server, etc). I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.