i have imported 5 excel files and linked to a query named stock.i have linked the code,description and quantity from each table (ms excel imported) to stock.
the problem is not all the codes available in the tables r available in stock.there r
more than 25000 codes but only 2614 r listed in stock.i dunno whether its the problem with the way i have linked.so if u can help me with the problem i will be really thankful.
5 excel sheets contains the code,description and qty of 5 shops.in the query stock contains 1st column-id,2nd column-code,3rd column-description and 4th,5th,6th,7th,8th column the qty from 5 excel sheets respectively.i have given the images below of 1st shop(similarly 2,3,4 and 5 r made).the image of relationship is also given.so pls help me.
i have imported 5 excel files and linked to a query named stock.i have linked the code,description and quantity from each table (ms excel imported) to stock. the problem is not all the codes available in the tables r available in stock.there r more than 25000 codes but only 2614 r listed in stock.i dunno whether its the problem with the way i have linked.so if u can help me with the problem i will be really thankful. 5 excel sheets contains the code,description and qty of 5 shops.in the query stock contains 1st column-id,2nd column-code,3rd column-description and 4th,5th,6th,7th,8th column the qty from 5 excel sheets respectively.
I have developed a Microsoft Access 2010 database for my client and the database is split with Front-end/Back-end, the Back-end and the database is shared on Network, The client operating system and applications for all users are hosted and consistent and the service is delivered over Citrix.
The database some times corrupt the tables record and give a permanent #Delete Error, I have attached one of the database table and the screenshot of the error,
I have a split database.(Access 2010).Three of the linked tables are Appointments, Appointments_OLD, Appointments_NEW.
I want to use vba to rename Appointments as Appointments_OLD (replacing the current one) and to rename Appointments_NEW as Appointments (replacing the current one)
I have used:
'replace Appointments_OLD by Appointments, replace Appointments by Appointments_NEW DoCmd.Rename "Appointments_OLD", acTable, "Appointments" DoCmd.CopyObject , "Appointments", acTable, , "Appointments_NEW"
Unfortunately this just made Appointments and Appointments_NEW clones of one another - changes in one automatically occur in the other.
What I actually want to do is to swap the names round in the backend database while maintaining the right links to the frontend.
This may be a simple question, but I'd like to reassure myself, so here goes. I have a database which is used to produce a pile of reports, and the tables are linked to a second database. I now need to update the reports, and add new data for this year. But the second database is password protected, and yep, I don't know that password. (I'm taking over this project from someone who left 3 years ago - I haven't just forgotten the password honest!)
Is it ok to import new data to the second database, and still carry on producing reports?
What if I want to change the properties in one of my linked tables? Can I delete the link, change the property, and then reapply the link?
Hi, There seem to be a serious bug in Access 97, and I want to know if anyone has encountered this before. I tried to describe the problem as best as I can, I also included a flash video to describe everything, here it is: http://naderchehab.googlepages.com/linkedtablebug.swf (~1 MB)
My database has two tables: - dbo_InventoryLocation which is linking to SQL Server 2000 via ODBC which contains inventory quantities for each product, and - tblPRODUCTS_BUSINESS which is linking to another access database on the network and contains product information (item number, title, description, and a field called "InvCategory" which describes item categories). I wrote a very simple query that links these two tables together:
SELECT tblPRODUCTS_BUSINESS.InvCategory, dbo_InventoryLocation.ItemNumber FROM tblPRODUCTS_BUSINESS INNER JOIN dbo_InventoryLocation ON tblPRODUCTS_BUSINESS.Item_Number = dbo_InventoryLocation.ItemNumber GROUP BY tblPRODUCTS_BUSINESS.InvCategory, dbo_InventoryLocation.ItemNumber ORDER BY tblPRODUCTS_BUSINESS.InvCategory;
The query really doesn't matter, it's just a test. Now here's the weird part: when I execute this query, and I scroll down to a specific category and count the number of items in that category, I see about 50 of them. But if I specify that category as a criteria, I see more than 200!
I also noticed that this problem does not happen when I use non-linked tables.
I hope anyone has an idea what that problem might be, and I hope there is a way around it.
We are sharing an Access 2003 database amongst two departments that contains twelve (12) tables that are SQL Server linked tables. Nine (9) of the tables work just fine for people in both departments. Three (3) of the tables cannot be opened until the user refreshes the link(s) using their ODBC DSN.
We do not understand why nine of the tables work without issue but three will not.
Has anyone ever run into this issue before? Does anyone have any ideas on how to resolve this issue?
I have been running queries and reports with data from our company database (a third-party system based on FoxPro2.6) by linking tables into MS Access 97 for years. We have now upgraded to XP and Office 2003, and suddenly I can no longer do this but get a message "Could not find installable ISAM".
I've tried to locate the required file on the Internet but no luck so far. Can anybody help me? I am currently using an old pc with Office 97 on it to run the reports, but it's in a different office location and makes the whole job very tedious. Are there any work-arounds?
Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks
SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType FROM MsysObjects WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16)) ORDER BY MsysObjects.Name;
Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks
Code:SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectTypeFROM MsysObjectsWHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))ORDER BY MsysObjects.Name;
I'm trying to use a template to track assets-specifically this template:URL....Is there a way I can link an excel doc to my database that provides all of my data, that is thereby linked to other tables within Access?I want my data to be updated as it is manipulated within the independent excel doc when I open access.
I am having the problem of importing a database from Excel/Msaccess 2000 to a Msaccess 2000 database from which the data was originally exported. The Database consists of two tables connected in a querry and the two tables has a unique primary key thru which the two tables are linked using a querry. While importing only two tables are listed in the importing wizard and not the querry.
So how to import the data to the two tables connected by a querry using a primary unique key.
I am really new to Access 2010 as I normally use SQL so im sure this is a very basic question. I have a form where I have linked some sql tables to a table in access so the data gets stored in SQL. The form consists of many fields the basic fields are Policy ID, Name, Office, Month, Dept etc. At the moment users have to fill this in using comboboxes and text boxes but I want to change this so when the user puts in the Policy ID all the other fields are populated ie in the office field it would have a sql query of or something that looksup the office from the policy id that is entered:
SELECT [Office] FROM DBO.DQ WHERE [Policy_ID]='what is entered into the Policy ID field' and so on.
Also not sure if this works but as I have now linked the tables to SQL does this mean that when a user fills in the information into the form it will then update the SQL table or is this another issue?
I recall reading somewhere (fairly recently) about a parameter or property in Access which is "On" by default and allows a form/query to identify when backend table field names or query arguments are changed and automatically looks for and "fixes" the renamed link. By leaving this switched ON, the database is slower than it could be. The db speed performance can be can be improved significantly by switching this 'feature' off. (i.e. Name of the property)
I'd like to secure my frontend by disabling access to the linked tables, queries etc. I thought converting to .accde would do this but apparently not. Is there a way that this can be achieved?
I am aware that I can disbale the ribbons etc but they can all be turned back on using the options menu.
I understand right off the bat if you're reaction is "don't duplicate data!!" -- mine would be too (don't fret, I know my normalization).
I've linked a table in my db to my Global Address Book in Outlook 2007 and, upon entering an employee number as a new record, would like to verify that the number entered is listed in the GAL and then pull in the associated name and location info.
The key is that I don't want this info to rely on the GAL going forward. For example, if an employee leaves or is no longer listed in the GAL, I don't want to lose the employee info (past data is needed for audit purposes). Note: I will be creating a report later to show if there are discrepancies between the GAL and my table, but that's another story...
So, what would be the best auto-fill options in Access 2007?
I have a question that I have a Microsoft Access database (.accdb) front-end/backend split and I want to give the database to my company client. As we have the different path for the backend/frontend linked. I want some code that will popup if the database location is not found and popup with the dialog so the user then select the backend and it would be ready and there is no need to popup each time the database open, it would run once it did not find the last linked path.
Also I have tried the code of Dev Ashish URL.... but unfortunately it would ask everytime to refresh the table links and I only want to run the process of linking tables when the database start and the linked path not found.
I have MDB database linked to SQL SERVER through VPN connection.I created links to the sql server Links are dsnless..Everything works fine but when I lost VPN connection or sql connection has been broken I can't refresh links to the tables.I receive message 3146 sql connection failed..I must close database and start again...
I tried different methods like ado,dao, and vba docmd.transferdatabase,aclink... but no success, table cant be relinked.
Only way I can relink is to change ip adress in conn.string
E.g. 192.124.0.2 (1st ip- router server ip) and after connection failed i can use 192.124.0.32 (2nd ip - server local ip) and that's it if i lost connection for the 3rd time... i must restart application.
It seems that access database keep the previous connection..how to reset or drop database connection to the sql server and refresh links to the tables with vba code without closing access database...
I have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase and S:datadata.mdb is now residing in O:masterdata and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so, 1. find unlinkable tables 2. search its new location under O:master 3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
I am sai new to this forum .. Guys i have doubt in MS access plz help me.. One of my frnd sent me a file which consists of some databases but they are in .mba file format and frankly i never heard about that file format and it was created by ms access 97 and i am using access 2003 .What the problem is when i am trying to extract those db files it is not accepting the ile format plz help me how to recover this i need it urgently hope u people help me..
I Have a table with multi date fields. I want to create a query that will display all dates in all fields that are greater than today ("<Date()") however when i enter this in a second query field criteria the result comes up blank
My question may be a stupid one...can we use command button to open a field?I mean I had a bound field in a form which is listing a hyperlink field from a table..I am able to open the link from the form...is there any way to link this field to a command button or any other controls....I am attaching a sample db with this to get an idea what I am asking.
Hi all, I have joined this forum recently, and would require the guidance of experienced members regarding a doubt:
I have tried to develop an access database with only one table in our office intranet. The front end has been developed on asp and is running on IIS 5 deployed on Windows XP machine with 512 MB RAM and P-4 processor.The system is working fine.
My doubt is ,will it matter, if more than one user is working on the database(with only one table).that is to say, suppose, one user is posting data to the table and another user is querying the table, will it lead to any complications? The situation as such is low concurrency, maybe about four users will be working simultaneously at the same time. Thanks in Advance, Sincerely, Manoj