I am trying to link a Progress table into Access. However, the Progress database table has 41 indexes. I have even tried to import and get the same error - Too many indexes - . Is there a way to get around this? Currently I am importing to Excel then linking Excel to Access. The table is time sensitive so a live link is the best. Any ideas? Thanks
View RepliesI am a beginner and can someone please help me with creating indexes( I think this is what I want to do)
I have a table for entering realtors
the primary key is realtorID (AutoNumber)
is there anyway to find if the realtor has been entered twice or prevent the realtor from being entered twice
thanks
Josh
Hello
Sorry to add to the number of 'relationship' posts but a thorough search has failed to turn up anything similar to my problem.
I have a table tblSite
Each geographical site (defined by a PK 'SiteNo') in tblSite can contain multiple occurrences of a large number of types of geographical features (ie multiple 1-many relationships).
Eg each Site can contain
100 trees
10 ponds
5 streams
2 springs and so on.
I could have anything up to 50 or so different types of feature that are related on a 1-many basis to the site, linked by 'SiteNo'
Access prevents any more than 32 indexes per table so in practice that means that you can't have more than 32 relationships with referential integrity enforced on any one table. I thought that I was correct in having a separate table for each feature type and creating 1-many relationships to the central table. The fact that I can't have more than a certain number of relationships suggests that my structure is wrong.
I thought about having one large table with a 'feature type' field plus all the other fields required for each feature type but the info recorded about each feature type is different, leading to a large table with many fields, many of which would be blank. This seems to be un-normalized to me.
So, my question is twofold really.
Have I got my structure right? Is it one of those special cases (we all want to be a special case?) and if so, how do I get round it?
If not, where have I gone wrong?
I thought I was comfortable with my design but coming up against this limit has thrown me a bit and I'm not sure how best to proceed.
regards
Neil
I am a beginner when it comes to access. The project that I am working on is tracking Travel claims. The claims are submitted ever month for 4 years so I have created 48 tables for each month and tried to link them via the SSN. The member can also submit other claims during this period, ie supplementals (for reimbursements that were missed in the monthly) and Concurrent duty claims (for the member going to additional sites then where they are assign).
I have a
MBR tbl - that holds all the MBR's info (SSN, name, unit, city, state, POC, POC email and Remarks)
(48) Partial tbl - linked by the SSN.
(5) Suppplemental tbl - Linked by the SSN ( I need more of these but I started with 5)
(5) Concurrent tbl - linked by the SSN ( I will need more of these)
These are all one-one relationship.
How can I get this to work. I can upload a blank copy of this database.
Thanks for any help
Craig
Hi guys. I got unbounded form that I want to add searching functionality by utilizing indexes. I would like to know how to add indexes for my customer table so that I can use it for searching records. I be happy if some one help with make indexes. Thanks
http://i5.photobucket.com/albums/y1...tabledesign.jpg
==> pic of my customer table in design view
I have sheet names stored in a text field.
sample data: (assume the field name as SheetName)1 (considered as an index) MySheet (considered as a Name)
How to handle them both in a single statement:vartype(SampleData!SheetName) returns 'Text' as Datatype for all values.
How to consider the numbers as indexes?
I am trying to consolidate a few tables I made to make them less numerous and hopefully easier to handle. At one point I received an error message that said I had exceeded the maximum number of indexes in a table (32) and thus it could not be saved. When I scroll through my table in design mode every box that has "Indexed" as an option is marked at "no". Many boxes do not have that option and thus I am not sure if those are automatically indexed(?), not indexed(?) or that indexing is not relevant for those values(?). I definitely do not have any, let alone 32+ boxes, that have "indexed" checked to "yes" though. They are all "no".
How do I figure out which columns in my table are indexed otherwise so that I can reduce those numbers and get more columns on my tables (by the way, I am no where near the limit of 255 columns on any of my tables)
I cannot cause a database to refrain from dropping several table indices during a Compact on Close operation. I am running Access 2010 under Windows xp.I have created a database that merges data in a linked text file with data in a linked database, appending the results in a local table. The database has the Compact on Close option set. The table has 27 indices (because most of the fields either are coded or are potential select or sort fields) and nearly 1 million records.
I run a delete query to clear the contents of the table, I close (and compact) the database, I reopen the database, and I run the append query. The indices in the target table are intact. I close (and compact) the database again; when I reopen the database, indices are missing -- sometimes about 10, other times nearly two dozen!
This table is linked to a larger system, which contains code to run the sequence outlined above. Whether run manually or in code, as stand-alone or as a component, indices are dropped. When I close the database with the table containing no records, the indices are not affected. After appending records, but before closing, the database contains 1.66GB. The drive on which the database resides has 42GB free.As suggested with regard to a number of other unexplainable issues, I have created a new database and imported all objects from the original database. The results persist. The larger system performs accurately without the indices, but the performance against a table this massive is horrible.
In month-end posting - I basically delete all the records from my transaction file. And in doing so - it seems to be resetting the auto number index - which is causing me major problems.
The resetting of the index after deleting all the records is normal.
I am working in VB 2010 - using a 2003 MS Access database.
This is a very simple problem most likely for the masses, but I am new to access. I have employees who enter will enter information about specific tests on electronics components into separate tables by a form. I know my method is poor, but this is how it works. I got assigned this database at my internship this summer because they ran out of thigns for me to do.....I've never even used access before, so as long as it works, they're happy. I have a form which which writes to a first table with 20 fields. I then have separate update queries which take the data in the first table and put it in all the other tables. Trust me, i know this is pretty much the opposite of the whole point of a relational database, but I am and was limited by time, for the size of the database they want, there was no time to learn about normalization etc etc. Sorry this is wordy, but THE QUESTION is......if they run multiple tests wtih the same information for some of the fields I have fields named "Run#, Unit#, Date" etc, even if there is records wtih the same information, hwo do i get it all to show. Right now, it seems to be rewriting over the same records. If they run 3 tests on unit 10 on August 1st, how do i get it so all those show up. I think its somethign wtih in the table, for the primary keys, changing the Index: No, Yes(Duplicates) Yes(No Duplicates) but I could be way off. Thanks, if the question doesnt make sense i'll try to rephrase it, I apologize I'm running on about 45 min of sleep:confused:
View 3 Replies View RelatedFolks,
i have a small problem wizzing my nut. I copy two tables from mycurrent database to an external database,now i am trying to link these two tables in the external database to the ones in the currentdb. I get a run-time error 3012 telling me that an object 'tblLanguage' is already present.which iam failing to track. Any ideas Please??
Here is my linking code.
Sub LinkMeUp()
Dim tbl As DAO.TableDef
Dim sTableName As String
Dim sThatTblpath As String
Dim dbName As String
dbName = "my_ExternalDatabase"
sThatTblpath = ";Database=" & db_Path & "" & dbName
For Each tbl In CurrentDb.TableDefs
sTableName = tbl.Name
Set tbl = CurrentDb.CreateTableDef(sTableName)
tbl.Connect = sThatTblpath
tbl.SourceTableName = sTableName
CurrentDb.TableDefs.Append tbl
Next tbl
End Sub
Hello,
The current database I am working on I made it by copying the one I was working on originally. I just realized on the VB window that there is still some relation between them. Because, if I look at the tree on the left of the code window I see it says "GCB (miniGCB)", being GCB the one I copied, and miniGCB the one I am working with.
How can I get rid of the link with GCB?? I want the tree to just say "miniGCB".
Thanks,
Hello all,
I created a table and linked a pdf file to one of the fields, but when I created a form to display the table contacts, I could not see the attachment or icon or any reference to the linked object. The field in my access table is an OLE object.
Thanks for helping out.
I have packaged an Access DB (Split) as a run time solution so I can distribute to users without Access.
When I install it on a PC it runs.
If I move the back end to a network, the solution can't find the back end because it has been moved.
Access run time does not include the linked table manager in the toolbar.
Is there code in VBA where I can refresh the link between front and back end.
Ideally I want to write an error handler that will call a sub to prompt the user for the new location of the back end if it has been moved. Then have the user browse to the network location and click on the back end and store that location as a string. Using that string refresh the links.
Any ideas on that?
Hi all,
I have added about 15 links on a form, linking to word and excel documents. The word documents open fine but all of my excel documents pop up with a messae saying the file cannot be open, yet when i just go to the document on my hard drive they all open fine...does anyone have any ideas please. Thanks
Hi,
I'm new to this so please go easy!
I have an excel document which automatically refreshes data gathered from an access db.
I need to put a button on the switchboard on the same db to open the excel document, basically to make it easy for others to find.
I have tried hyperlinks but it doesn't want to play. It locks the db and then won't refresh the information.
I know i'm missing something blindingly obvious, can someone help?!
:confused:
Thanks
elsiegee
Hi Forum,
I'm building a db to track computer and user problems for work, where I double as a Client Support Administrator (IT Guy).
I have these tables: (simplified list)
tblComputers
tblPeople
tblCompAssignment
tblProblems
I'm at a loss as to how to link the 'Problems' table. This is where I'll record trouble tickets reported by users.
Incoming trouble tickets could relate to the Computer or to the User's Network Login.
I thought I might have a field in the problems table for UserName and also one for ComputerID, but this seems problematic. Anyone got any ideas?
I am trying to link to a url from a message box with difficulty.
eg the user opens the form and a message box appears asking have the latest datasets been downloaded? If answered yes the form opens, if no links to web address.
has anybody any examples.
Thanks
I have a database which works as an information system for the upcomming olympics.
In tblAthletes is...
AthleteID (Primary Key)
FirstName
Surname
And Various other fields not needed in this problem
In tblResults is...
Gold
Silver
Bronze
Also other field in this table
What I am aiming for is to have Gold AND Silver AND Bronze all as foreign keys of the AthleteID field.
Is there a way to do this or can another way be suggested. I am working on the 2000 version of access.
Hi There
i am trying to link some more tables to my access front end from the sql server back end
when i select 'ODBC Databases' from the link pop up box, the pop up box just closes
any ideas why, or work arounds would be appreciated
Happy Christmas
Does anyone know how to link tables automatically/VBA?
I'm trying to import files from our DB2 database in which one file is created monthly and instead of manually importing / linking the files manually, I'm looking to have access link to the new file automatically.
The file names are structured as so..
ITM01 (Jan)
ITM02 (Feb)
ITM03 (March)
Thanks
Hello everybody,
I have one dbase with 3 tables wiht 3 forms. One is "custonmers" , "glass" and "lens".
Customers - CusID as primary Keys
Glass - GlasID and CusID primary keys
Lens - LensID and CusID Primary Keys
When I change a specific field (only) I am losing the records on the other tables of the specific customer.
Thanks in advance
xxx ΠΑΟ ΘΡΗΣΚΕΙΑ ΘΥΡΑ 13
Hi All
I enter data in a form "WIP" that is based on a table named "WIP" in the form I have a command button. When I activate the command button it takes me into another form "Materials" which is based on a table named "Materials".
The data that I enter into "Materials" is not linked to the data ie Customer details which I have entered into "WIP".
My aim is to be able to report the materials used.
I have tried variations of relationships but am obviously missing something.
Thanks in Advance
Geoffk
Is it possible i create one master table and link few table together??
In the mdb that i attached there have 3 table - tblMaster, tblConfiguration1 and tblConfiguration2.
Using the relationship to link tblMaster's field Configuration to other 2 table Configuration field but there only showing 1 table data.
Is it allow in tblMaster able to show this 2 table together??
I have a FE database that on initial setup the user will enter the drive letter of the mapped location where the BE resides. The FE will then update all the linked tables to that path. This should work but for some reason the FE locks up (stops responding) when I test this. Any suggestions as to why. It doesn't stop responding until this part of the code:
tdf.Refreshlink
I have looked and it does change the link path, but it locks up.
Dim dbs As DAO.database
Dim dataPath As String
Dim tdf As DAO.TableDef
dataPath = Me.drive & ":IMP1_R2.2_be.mdb"
For Each tdf In dbs.TableDefs
'Not a MSys type table
'If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
If InStr(tdf.name, "MSys") = 0 Then
tdf.Connect = ";DATABASE=" & dataPath
End If
err = 0
On Error Resume Next
tdf.RefreshLink
End If
Next tdf
DoCmd.Echo True, "Done"
Hi
i would like to link to a query. when i go to file | Get External Data | Link Tables, i don't see my queries. is this possible??? and if so how?
sam