Queries :: Cannot Locate Source Of Table References
Feb 24, 2015
I have a database I took over, that generally uses linked tables to a MYSQL servers on another machine. The Access database uses a machine DSN to connect.
There are many queries that contain references to linked tables using another name. For example, the table is "service unit" but the queries are using "SU" as a reference. I cannot find any database object with that name SU. There are many such tables referenced like this - all uppercase letters, which is a common thing they all share.
Now, the server had a file integrity issue, and now the queries that contained the SU references will not work anymore. I created a linked table and called it SU and but the queries still won't work until I edit the queries, remove the old SU table, and add the new one in. So,
1. Where those table references could have come from, if it's not a linked table? Is there a hidden location on the server, or in the access database that stores another connection method?
2. Why won't simply adding a similarly named database object work the same way? Why must I re-edit all the queries?
I got a combo box with a few columns for locating query activity. However, how can I make use of it to locate a particular record according to predefined values, for instance in a loop, to locate records. The combo box got 3 columns, just want to know the syntax to define the combo box according to some values. I need to define only the first colmun or all 3 columns??
I have a table that was linked from a SQL Server with a file DSN. It was linked years ago so now I want to locate that specific DSN file for that specific table if possible.
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
I have a fairly simple append query that appends two columns of data to another table - all good. Except, the destination table has a field 'ServiceDate' that I would also like to be completed at the same time with today's date. I presume that this is =Date(), but where do I put it to make this happen?
I am trying to create a database which crosschecks specifications against building codes. My problem is that it's not feasable to put all of the information about a Building into the Room table, though many codes require information about the Building in order to classify the room. How would I count (or list/get details from) a room in the Rooms table, for use in an SQL statement about the Buildings table.If there are up to 5 rooms in the building, do at least 2 rooms contain a fire escape?
My Rooms table contains information about if the room has a fire escape, as well as references its parent Building ID. How would I query the rooms to count the Rooms within all buildings, and subsequently query all Buildings which have less than 2 rooms with fire escapes?I need it to return a list of the buildings which do not conform to this code.
Now this is literally my second day of programming/working with Access, but I've yet to find an answer dispite hours of searching.I think I can adapt it for the 100+ building codes/references I'll need to implement. It would be perfect to find an SQL-only way of doing this, without going into modules etc...
I'm quite new to Access and have inherited a database from a colleague. It is a database of references from which we have extracted pesticide active ingredient, pest species and crop species using forms. I am trying to create a query that will allow us to search and return unique active ingredient x pest x crop species combinations (but which will return all instances of this combination).
The complication is that the pest and crop species names are within the same field ('taxa'). They are distinguished by a label ('pest' or 'crop') in an associated field ('PestorPredator'), but a query using 'OR' will only return a crop OR pest, whilst a query using 'AND' will return no records (because something can't be a pest AND and crop).
I want to be able to create a table that shows the active ingredient, associated pest species AND the associated crop species each in separate columns. We have tried a couple of methods, including UNION and UNION ALL....
I have a table called Tbl_Query_List that houses the query names that I want to be displayed on a continuos form. I want the user to select the query by highlighting it in the continious form and then having a command button at the bottom of the form.
I have everything setup including the command button. Assuming that on the EVENTS tab I set the OnClick to [Event Procedure] but when I click on the ellipsis what do I type to make it run the highlighted query?
The problem I having is if I have a computer name of a computer on our network (not the one I am running the database on), is there a way using VBa to find the details about this machine.
For example if the computer name is 'DTP002133' I can click 'start' then 'search for files and folders' then click 'computers' in the search for other items. This search will give me the computer location and comments. I would like to be able to automate this process within my database.
Not sure if this one is possible but thought I'd pick you brains...
I'm very new to Access and especially VBA. How to get started with the correct syntax for locating a file, and printing it. The file will be a PDF and if possible I would like to declare a variable for the file name (not the path) so if it is changed I can just change the variable instead of looking for it in the code. I plan to have 10 of these.
I have an MS Access application which works fine until used with XP OS.
In XP if you open a report which is based on an ODBC linked table and try to send in RTF format as an attachement you get this error message:
MSACCESS.EXE - Unable to locate component This application has failed to start because BJABLR32.dll was not found. Re-installing the application may fix this problem.
The error only occurs for reports using ODBC tables as a data source for which you need to enter an ID and password. Other reports can be sent normally using Outlook etc.
In order to act as a system, I would like to have a function or windows firstly displayed all the folder names in a drive, and then allow user to locate/select freely a folder among different folders. Is there anything for my reference or for me to start??
I am trying to locate a record on a multirecord form by using Findfirst method. Here is the code.
Dim UA1 As String, UAE1 As String, UA2 As String, UAE2 As String, UA3 As String, UAE3 As String, apost as string, repl as string apost = "'" repl = "''" UA1 = Nz(Forms(ParName).Form.NAME, " ") UAE1 = Replace(UA1, apost, repl)
[Code] ....
This code sometimes works and sometimes it does not work. The field CNO is a text field of 5 characters but contains the card numbers that is numeric data or nothing.
I have created a code below to test whether I can run a query and retrieve a data from an SQL server table. And so far I can return the result using a messagebox. but somehow I just don't know how to use this connection to update the table inside this access file. Basically I want to use this as a front end file. then when the form is open it will automatically update the table inside this access file and load the data to the combo box as a list.
Code:
Option Compare Database Sub LocalServerConn_Test() Set conn = New adodb.Connection Set rst = New adodb.Recordset
When I have a 3rd table looking at the row source of the 2nd table, which is looking at the row source of the 1st table. I only get numbers.
Everything I have read so far points to using a query as a solution. However, the query I made is not updating the information from the 2nd table.
I have attached a few examples.
Attachment 6247
this below shows. I changed "Bakersfield-test1" in the original table. However, the query I made does not update, still showing "Bakersfield-test". When you click on the drop down, it shows the updated "Bakersfield-test1", but it does not repopulate my column with the updated info from the table.
If I have a form with multiple list boxes, lets say named 10, 20 and 30. and multiple text fields called 15,25,and 35. Now I want to get a value in the list box based on the text box value so I make row source queries that looks like this.
SELECT [table].field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![15])); SELECT [table].field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![25])); SELECT [table].field1,[table1].field2 FROM [table1] WHERE ((([table1].field2)=[Forms]![form1]![35]));
So now my question... Is it possible to make one query that picks up the 15,25 or 35 depending on wether it is being queried from 10, 20, or 35. The difference in name is always the same except for the first number so could it be made to reference where it is asked from?
I know my form is large and got too many fields for a standard form but trying to expand it anyway and don't really want to go to sub-forms...as then cannot expand it without re-creating forms/queries/tables structure. (or that what i believe)
My form and subsequent table is nearing the 255 limit, so I created a second table and just used a query to put both in form, but then I found out that a query also has the limit...
So if have two queries set up from each table but to same form?
Currently I have over 600 columns I need to use in a query and since the limit is 255 columns per query, I need to create multiple queries. Currently the form shows the record source of the single query I have created. Now that I need to create the other queries, how do I have the forms record source to recognize the other queries as sources as well?
I am after getting an access 2003 database to look after and it contains SQL pass through query's. The database is a front end to a MS SQL server database with a connection string that is contained in a module.
I believe the SQL pass through queries are connecting to the same database as the rest of the application and somehow is using the connection string in the module. However I cannot find how that is configured on the SQL pass through queries.
Most documentation on the net seems to point at using ODBC to connect slq pass through queries to outside databases but I don't think this is the case. There are not Odbc sources set up for the database I am look at.
I have a database that reports activities by region.
Each week, my regional volunteers report statistics on a number of club activities. This is in the form of zero to theoretically infinite activity reports that they enter on a website. I download the .csv from the website, add the activity reports to the activity table and send them a totals summary every now and then.
The summary report shows figures for every club in the region, even if no activity reports have been entered for that club that week or ever.
This works fine, including forcing the query to return zeros when no reports have been submitted for that club.
What I want to do is have the report also show (in brackets next to each figure) the position as it was X number of days previously.
I can make the query and report to show the figures now.
I can make the query and report to show the figures X days ago.
What I cannot work out is how to combine the two queries into one report source so that I can get
Club 1 100(50) 75(0) 45(45) Club 2 0(0) 0(0) 0(0) Club 3 20(19) 0(0) 200(50)
etc
If I try and make a third query that gets the sums from qryNow and the sums from qryXdaysago for each record in qryClubsByRegion, I get two lines for each club.
I have designed a couple of DB's using Access 2002 in Access 2000 format. While these all work perfectly in Access 2002 there a few users still using Access 2000 and are experiencing errors (specifically with Graph.exe which is 10.0 in 2002 and 9.0 in 2000 and also with the Calendar Control which is 10.0 in 2002 and 9.0 in 2000).
Is there a way to use the 2002 versions of these controls in 2000 or do I have to create the DB using the Access 2000 versions (in which case would I need to design the DB in Access 2000 instead of 2002).
Can anyone suggest anything or point me in the right direction.
Anyone seen this before? The scenario is - A distributed application on ten machines - all running the MDE in runtime. All machines are running perfectly. Minor changes made to the application and new MDE built. After roll-out, six machines work perfectly with the new version. The remaining four run the application but experience the dreaded "#Name" in various places. Revert back to the previous version - and all run perfectly again. Cannot be missing files - have not called for any and previous version works. Cannot be broken references - six are running OK. Cannot investigate on any of the machines because they are running MDEs. ??
I have a form that has five subforms on it. On Open all the subforms are unbound (so i can prevent the queries from running before the On Open event to speed up). Then i can enter my criteria for all five subforms in txtbox and click run. After I establish the source object for the subforms, i cannot establish the controlsource for several text boxes that pull the data from the subform, i get #Name?. her is a sample from the on click event... the top links the unbound subform and the bottom should then link a control in the subform to a control on the main form.
Me.Child167.SourceObject = "query.RP Sum Fuelman F1" Me.Text71.ControlSource = "=[RP Sum Fuelman F1 Subform]![cntRecords]"
I am trying to create a form with multiple combo boxes where users can select fields from my main database and click a button to generate a report based on their selections. I think I am supposed to create a generic report with perhaps 4 fields where I would link the four combo boxes to. The issue is I cant seem to get the selection of the combo box to change the field that the report should pull from the database. I think I am supposed to use dlookup in the reports control source for each field, but I cant seem to get it to work.
Is it possible to set which VBA reference boxes are ticked each time a database is loaded?
A problem is created when a person amending the database to the person using it has different setups and so some of the references show as "missing" and so compile errors show on loading.
