Indirect Table-linking Problem

Nov 2, 2007

I'm using Access 2003 under Windows XP.

I have a very large linked table that is growing daily, and will soon exceed the 2Gb size limit of a single MDB file. So, my management have told me to host this table in their big Oracle system that does not have this limit.

However, there are many hundreds of Excel spreadsheets and tens of other Access databases on the network that explicitly use this linked table - so we do not want to change its referenced name and location if possible.

So, we thought we could keep the current linked-table's MDB file, and replace the local data table with a link to the replacement Oracle table, so that everything would look the same, and nothing external would need to be changed.

Unfortunately, Access does not let you link to a linked table - you have to link to the actual table directly.

So, we thought that we could fool it by writing an Access pass-through query in the current linked-table's MDB file that would SELECT * from the Oracle table - and name the Access pass-through query the same as the original table.

This all works fine if you open the pass-through query or write a test SQL query against it - though I suspect we may hit the 2Gb problem anyway, if it tries to make a temp table.

However, there seems to be a "feature" in Access that when you try to make an external link to that query, the Linked Table Manager says that "The Microsoft Jet database engine cannot find the input table or query 'MyTableQuery'. Make sure it exists and that its name is spelled correctly".

But, the query is definitely there and definitely spelled correctly. I'm copying and pasting the location directly from the address bar in the Explorer window, so I'm not using the wrong database. And, it must be possible to link to queries, else it wouldn't refer to queries in the error message.

I've even tried remaking the database using the undocumented /decompile MSACCESS.EXE command switch to be sure there aren't any dud references lying about.

Can anyone help?

Ron
(Access 2003 under Windows XP)

View Replies


ADVERTISEMENT

Obtain Hierarchy Reports (direct And Indirect)

Jun 10, 2014

Any way to obtain all individuals that report to a particular person (directly or indirectly) through a query. A sample of the table is below:

tbl_Hierarchy
Enumber
FName
Mngr_Enumber

E111
John
E000

E222
Mary
E111

E333
Paul
E111

E444
Sarah
E999

E555
Sam
E333

E666
Tom
E111

E777
Karen
E111

E888
Jim
E666

E999
Deb
E666

For example I would like to see everyone who reports through Tom(E666). The result should yield:

E888
Jim
E111

E999
Deb
E666

E444
Sarah
E999

View 3 Replies View Related

Table Linking

May 19, 2005

Ok...I have an issue that I don't know how to resolve.
I have a table called 'orders' in the orders table there is a place for " quantity', 'colours' and per unit. The cost per unit is based on quantity and colours.
For example: 12-35 shirts 1 colour are $2.85 per unit. 12-35 2 colour are $3.50 and so on.
I would like to be able to put in the quantity and # of colours and the Unit price fills in a field.
I don't know how I can accomplish this.

View 2 Replies View Related

Linking Just One Table

Sep 4, 2006

I have created an Access database application that is stand alone for each account manager/client at our business except for one table that I want shared as a source of data across all separate databases that are created.

I've investigated automatic Database Splitting and this works, except I only want the one table to be split and the others remain unique to each database.

I tried exporting the table in question to another Access database and then linking the table into each other database, this seems to work except that referential integrity can't be set up as it was. Is there any way to keep the integrity between the external database and the databases it links to?

Thanks in advance for any advice.

View 1 Replies View Related

Linking A Table

Jan 20, 2005

Hoi,

I'm using 2 databases. The first is my working database (LiTiMA.mdb) the second database (LiTiPA.mdb) is just for the password table.
There is no link between the 2 databases. But when I open a form for editing the password in LiTiMA, a connection need to be created between the 2 databases.
If the form is closed the connection need to be removed.

Does someone know how to do this?

thanks
Koen

View 4 Replies View Related

Linking Paradox Table

Oct 30, 2006

I have an access dbase trying to link a paradox table. I have Office 2003 and BDE 5.01 was installed on my machine. When I try to link to a paradox table its giving me an error "Unexpected Error from xternal database driver (11265)". On the other hand, I have Office XP on my laptop and BDE 5.01 installed, I tried to link to a paradox table and it works. :eek: :confused: What can I do to link to a paradox table using Office 2003? :confused:

View 1 Replies View Related

Table Linking Query

Apr 1, 2008

Hi there,

The aim of this (as part of a bigger solution) is to create an entry system where users can enter jobs in, assign a resource to the job and give the resource a cost. The conditions are that resources can be on more than 1 job, more than 1 resource can be on a job.

Obviously entering in resources manually is not a good solutioon (eg having 20 resource fields) as it is now. I want to create a relation between tables to achieve something better.

Let's say I had (for illustration sake) 2 tables, one which is a list of people and one which is a list of jobs. I want to be able to assign a person to a job for x amount of time (defined by a start date and end date in the job table) at cost y.

So we could have a field for the people table which would relate to the job id and a field for rate in the job db. However, people can be on more than 1 job (eg after doing one they do another) and there can be multiple people on a job (having one field for rate rendering it useless)

How can this be done in the most efficient way so that any job could be looked at historically and have the relevant rates etc? Would I have to create another table with rates relating to the job ID and person ID? This would allow for multiple resources per job and to have a rate assigned for each resource.

eg if person ID = 1 and job id = 1 then fetch rate etc and display in a list, and to calculate total cost simply sum all rates where job id = 1?

Thanks

View 3 Replies View Related

Linking Datawarehouse To A Table

Nov 18, 2006

Hi guys,

I have been linking text delimited file to my Access table. The text delimited file is generated by a report run by our in-house system. I've been able to do this. Now, I want to learn how to link a datawarehouse (ODBC?) cube to Access. Is there a tutorial somewhere I can perhaps start? I tried to search this form by typing "Linking datawarehouse" I only found one post.

Thanks

View 6 Replies View Related

Table Not Linking To Query

Sep 25, 2007

I am pretty new to creating relationships between tables. I have a main form with subforms embedded. I tried running queries from the main form and they run just fine. I also run from the tables linked to the subforms just fine. however, when I try to run a query for a report that takes data from the main form and joins with data from the tables linked to the subforms all the data cells are blank. I have a linking table which is also blank. All the forms are linked by the same program ID number. Any ideas on how to resolve this? Thanks

View 14 Replies View Related

Linking Records In Same Table

Oct 10, 2007

Hi,
I have been thinking of allowing our users to be able to link records together within our database.
We have a CRM system that lists customers and their details of mortgages and other insurance related products which they have. Some entries maybe linked in some way, possibly other family members etc.

My question is, is it possible to perform this kind of action? So they can then see relationships between either related records (ID ref etc).

I'm sure I've seen this in SQL based applications, but haven't come across it within Access.

Thanks
Matt

View 2 Replies View Related

Linking An Expression With A Second Table

Jan 2, 2006

When running a query i have created an expression which is simply 2 fields joined together. JOBLOC:[JOB]&[LOCATION]. I want to link this expression with a field in a second table [SCOPE], but for the life of me can't remember how i did it. Either in query design or SQL.

Thanks for your help!

View 1 Replies View Related

Linking Table Question

Jan 30, 2007

Hi.
I have a table with Basic Client Data including their program start date.
One type of service they might, but not necessarily, get is job training. So I have a Training table.

I need a query that will show all client's names (found in Basic Client Data) with a specific start date but exclude anyone who has a specific job training service.

How to set up?

Russ

View 1 Replies View Related

Linking Form To Table?

Apr 10, 2005

Hi there,

I just added a new field to a table but i now need to add the field into a form so the table can be update. However when i open the field list to assign/bind the control the new cloumn is not present on the field list.

How do i add a new field which i have entered in the table to the field list?

Thanks Mark Flavin

View 5 Replies View Related

Form And Table Linking

May 28, 2005

Hi, i'm trying to get a field to only allow data to be entered that is in a table
e.g. i have a table which has a list of country dialling codes in it. In my form i want to only allow valid country codes to be entered-i.e. the ones which are in the table?
Ive tried coming up with different validation rules but cant seem to do it
Can anyone help?

View 2 Replies View Related

Linking Form To Table

Sep 15, 2005

I am creating a Risk Management Database at work. I have created a tab with options to choose, as to log the risk relevant.

i.e. I have a catagory, subcatagory and then Risk boxes

When saving the record it will save the risk but it will not save the catagory, and the subcatagory options choosen? I have a seperate table for this information to appear, Does anyone know how I can make this happen without stopping it from shortening the catagories down.

I hope this make sense. I am sorry I am a little rusty in Access 2003.

Thanks,

View 1 Replies View Related

Linking A Table To A Tab Control

Mar 15, 2006

I need help with this form I am creating. I have an application form on one tab control. I have created a second tab control, called Children. I want to link that tab control to the table called "children". The other tab control, application is linked to the table called applications. Can I link my second tab control to a different table? Thanks.

View 11 Replies View Related

Linking 1 Table To Another With Y/n Options

Jul 27, 2006

I’m really new to access, learned through video professor. I have access 2000 Question: my main table contains basic client info. There is a yes/no field on it. When I click on the box in the form I would like to link it up with another table that I will create with some of the fields already filled out (name, Phone…) from the first table.
Example: the y/n box is labeled as “Buyer” if I click yes in the form; I want to create another table that asks more questions about the ones who are buyers only. In the second table, I also want a few of the fields to fill in automatically with some of the info from the first table (ID#, name, phone#...).
thanks

View 1 Replies View Related

Created Linking Table

Mar 12, 2013

I have a database with the following tables (among others):

tbl_Projects
tbl_Staff
tbl_ProjectsStaff
tbl_Payroll
tbl_Training
tbl_Clients

tbl_ProjectClients
tbl_ClientContracts
tbl_CostEstimates

As you can see I created two linking tables (project/staff and project/client).After that I linked other tables to the liking table. For example, tbl_Payroll and tbl_Training are linked to the tbl_ProjectStaff primary key.

I don't know much about access so I wanted to check if this is a correct design. I create forms that work well, but I noticed that the linking table doesn't have any data in it. The payroll table and the training table have the staff iDs and Project IDs but the tbl_ProjectStaff is empty.

View 14 Replies View Related

Associating 'linking' Records From Within The Same Table

May 17, 2005

Hi all, here's a challenge for you:

I'm building an access2000 database for the development department for a nonprofit. The database houses a contact list which is populated by the names, addresses etc. of all the people that have donated money. The organization will often receive a gift from the husband OR the wife - so we want to have 'gift histories' which isolate a unique record (the individual - in this case we don't want to send a thankyou letter to John AND Mary Smith for a gift that came in from ONLY John Smith). However, when the organization has a fundraising event, or wants to identify how much a specific household has donated then we want to run mail merges and reports that pull information from both the husband AND the wife's records.

Currently the only to do these things is a manuel process. We have records for:
John Smith (husband)
Marry Smith (Wife)
THEN WE ALSO HAVE
John and Mary Smith

Therefore, whenever we want to send out a mailing or generate a report we have to delete duplicate addresses from the mail query report, making sure we use the appropriate record for the mailing.

Thus, my question is this:

Is there a way to associate two records from within the same table? That way I could:

run a report for just 'John Smith' OR John Smith PLUS affiliate record.

Send a mailing to 'John Smith' OR send a single mailing to John smith AND affiliate.

This very simple task is actually quite difficult because access doesn't want me to link two records from within the same table. Any suggestions?

Thank you in advance for your thoughts.

- John

View 2 Replies View Related

Access 97 And Table Linking Problem

Sep 19, 2005

Hello

When linking in a table under Access 97, any long integer fields are being converted to TEXT format. This I understand is due to the version of the Access database engine.

It does not happen in Access 2000.

Anyone have any idea's of how I can force access 97 to use the jet 4 engine installed already ?

( i have abused the registry - and it didnt like it at all )

tried service packs etc - not joy so far.

Anyone have any idea's ?

thanks in advance

View 2 Replies View Related

Corrupted Records After Linking Table

Dec 3, 2004

Attempted to link to a table on an SQL Server, which included customer records & various contact dates, after the importing the records became corrupted in that they repeated the initial date over & over e.g.

Customer Date
AB1111 16.11.04
AB1111 16.11.04
AB1111 16.11.04
AB1111 16.11.04

...as opposed to listing same account number but with 4 different contact dates...also when the Access database was opened on a different PC (also Office 2K) the table was displayed correctly?

Has anyone else come accross the same issue, any advice/guidance much appreciated. :rolleyes:

View 1 Replies View Related

Trouble Linking Table Fields

Dec 14, 2004

Hello,

I created a sys admin database and the big problem I am having is linking the hostname field in table 1 to the hostname field in table 2. I have tried making both fields primary keys, I have tried creating a relationship to the two fields between the two tables. I have tried adjusting the join types.

Yet, no matter what I try, the hostnames from table 1 will not populate in table 2. I have even tried adding a new hostname to see if it would populate and it won't.

any ideas?

Thanks,

stillabeginr

View 2 Replies View Related

Linking An External Table Through ADO - Not Working!

Feb 17, 2005

code Fails at appending new table to catalog,
i am missing something, don'tknow what though,

thanks in advance,
sportsguy


Private Const strProvider As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="

Private Sub ADOLinkProjMaster_DblClick(Cancel As Integer)

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

cnn.Open strProvider & "F:PROJMASTER.mdb"
Debug.Print cnn.ConnectionString

Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog

Dim tblLink As ADOX.Table
Set tblLink = New ADOX.Table

' Open the catalog.
cat.ActiveConnection = cnn


With tblLink
' Name the new Table and set its ParentCatalog property to the
' open Catalog to allow access to the Properties collection.
.Name = "PROJMSTR"
Set .ParentCatalog = cat
' Set the properties to create the link.
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Datasource") = strProvider & "F:PROJMASTER.mdb"
.Properties("Jet OLEDB:Remote Table Name") = "PROJMASTER"
End With

' Append the table to the Tables collection.
cat.Tables.Append tblLink


Set cat = Nothing

End Sub


what am i missing??

oh, and i shouldn't see the table in the db container either, correct?

View 3 Replies View Related

Linking Data In A Table To A Form

Jul 10, 2006

hi, i have a table database created and i would like the information in it to be able to be linked to a form. but only certain information in the table, not all.

View 1 Replies View Related

Import And Linking Table Problem!

Jun 7, 2007

I'm linking an Excel Table to Access, but when I link it Access doesn't read some of the fields because Access pre-assigns the data type based on the first couple of records. The fields I have are Numbers and Text combined and Access declares them as TEXT, so when the fields are all #'s Access shows them as "#Num!." Also I have tried to import the sheet but again Access dictates the fields so it'll declare some columns has NUMBER so the fields with text have an error. How can I solve both problems?

View 9 Replies View Related

Linking Table To Excel File

Jul 31, 2007

I have a table in Access databse link to excel file in the network, the format of the fields of this file are set to general. When I create a link table wizard the fileds formats changes to numbers for some of them. It's wierd, I spent a lot of time figuring out what is the problem and creating new excel file with changing the fields format around and still not solving the problem. Any idea or help for this problem is apperciated. Thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved