Not exactly sure if we has anything to do with sql server so sorry if it isnt strictly relevant.
I have a database on sql server and am using an access data project as the front end. Have finsihed this and is working fine till I try and use it on another computer on the network. It still connects to the database fine (well on most machines on the network not sure why not on the others) but the problem is access functions in my reports and forms seem to have stopped working and show up as ?#name
Examples of the kind of functions I am talking about are
=Format$([DateFrom],"Long Date")
=[SumOfMinutes]60 & Format([SumOfMinutes] Mod 60,":00")
I recently upgraded to Access XP (2002) and now my application will not respond to F11 to get into the development window. This application has two mdb files. One is the database (with security), and the other one has the forms, queries, reports and links to the tables in the first mdb.
I developed a database in 2005 and used the 'Left' function in many of the queries to obtain the the correct text for output to reports. The database worked extremely well for a year and a half.
I have not accessed it for the past year. Recently I opened the database for use with a new project and I keep receiving the error message:
! Undefined function 'Left' in expression.
I haven't changed any of the expressions and do not understand why this funtion no longer works.
My access installation opens databases reasonably well. I can create databases, add tables, etc. However, I cannot:
1) Add a command button or data-related fields to a form: the "command button wizard" stops responding on the first screen. If I click "Cancel", the button depresses, but the wizard doesn't close. 2) open the "Linked table manager": Access crashes 3) import an Excel spreadsheet: Access crashes
I suspect these might be related.
I've tried these with various databases, some on my local C drive. I have also tried these with various excel files. Not being able to add data items to forms is particularly irritating -- I don't want to build my own front end (that would sort of ruin the purpose of using Access, except that it happens to be a database already on my desktop).
One other thing -- when I start up a database, it always gives me an initial "Installing components" message. This occurs similarly for other MS office applications as well. I don't know whether this is related. I do have admin rights on my local machine, and have tried the "Repair MS Office" function in the Control Panel.
I don't necessarily expect an "answer", though some advice on how to proceed would be great.
Hi, I have a database in Access 2000 format that runs just fine on my work PC which is running XP Pro and Access 2003. When I run it on my laptop (Vista Ultimate and Access 2002) I have a problem where it doesn't recognise the Mid function (or Left, right etc...for that matter). Everytime I try to run the query I get an error 3085 Undefined Function 'Mid' in Expression. Other databases work fine, it just seems to be this one. Can you please explain why this is happening and how I can get around it. Cheers
I created a DB program for my last job that resided on a stand alone PC. It has the Access generated user level security (so there's a "secured.mdb" file) and can only be opened via the desktop icon. In the past, when I copeid the db to a flash drive, took it home to work on it, it always said I didn't have permissions to run it unless I placed it in the right spot on my computer where I've got it set up with a secured file and the desktop to open it.
Yesterday, I noticed that I can now open it anywhere, including my laptop, and it just opens. My concern is, I don't trust the business where the program is being used to not take it and call it their own (I trust the management team, however).
ANy ideas why it no longer looks for the user level security now?
I'm running XP Pro on both machines, Access 2002 on the desktop and 2003 on the laptop.
I'm using Access 2000 along with Oulook 2003. Since we changed email servers, the send report no longer works. The addresses no longer appear to be valid. I can manually put all the names in every time an email is sent, but that is defeating the purpose!
I even tried changing the addresses to what an outsider would use rather than our internal name list; (milko@valspar.com instead of Mary Ilko) but this didn't work either.
Here's the code: Private Sub cmdMailApproval_Click() OnError GoTo Err_cmdMailApprovalClick
I changed a form in the design master that had nothing to do with the date or a date field. Everything appeared fine.
Now I updated the replicas, and an error is happening on all replicas as well as the design master. Any reference to Date() or Now() causes errors.
If it's a field on a form it has an #Name error. If Date() or Now() is on a report, a popup to enter parameters appear for the date. This database with these codes have been working for years.
Is it safe to say it's no longer recognizing Date() or Now() ?
Situation: I have written a fairly straight forward nested query which aggregates sales & activity data (num_calls, date, etc.) by customer specialty (CODE_ESP_ACC1) in a single query. The SQL I have written to achieve this is as follows:
SELECT Q002.GP, Sum(Q002.Num_Calls) AS Total_Calls FROM (SELECT Q001.CODE_ESP_ACC1, Count(Q001.ACT_ID) AS [Num_Calls], Iif([Q001].[code_esp_acc1]="MG" or [q001].[code_esp_acc1]="MF",1,0) AS GP FROM (SELECT [06 Act 2005 Hosp - IM].ACT_ID, [06 Act 2005 Hosp - IM].TER, [06 Act 2005 Hosp - IM].ACC_ID, [11 Physicians with main specialty].CODE_ESP_ACC1, [06 Act 2005 Hosp - IM].DATE FROM [06 Act 2005 Hosp - IM] LEFT JOIN [11 Physicians with main specialty] ON [06 Act 2005 Hosp - IM].ACC_ID = [11 Physicians with main specialty].ID_ACC WHERE ((([06 Act 2005 Hosp - IM].POSITION)=1))) AS Q001 GROUP BY q001.code_esp_acc1, Iif([q001].[code_esp_acc1]="MG" Or [code_esp_acc1]="MF",1,0) HAVING (((Q001.code_esp_acc1) Is Not Null))) AS Q002 GROUP BY Q002.GP;
This works absolutely fine. I can save this query and run it many times without problems. However, if I then re-enter SQL edit view, Access reformats it as follows:
SELECT Q002.GP, Sum(Q002.Num_Calls) AS Total_Calls FROM [SELECT Q001.CODE_ESP_ACC1, Count(Q001.ACT_ID) AS [Num_Calls], Iif([Q001].[code_esp_acc1]="MG" or [q001].[code_esp_acc1]="MF",1,0) AS GP FROM (SELECT [06 Act 2005 Hosp - IM].ACT_ID, [06 Act 2005 Hosp - IM].TER, [06 Act 2005 Hosp - IM].ACC_ID, [11 Physicians with main specialty].CODE_ESP_ACC1, [06 Act 2005 Hosp - IM].DATE FROM [06 Act 2005 Hosp - IM] LEFT JOIN [11 Physicians with main specialty] ON [06 Act 2005 Hosp - IM].ACC_ID = [11 Physicians with main specialty].ID_ACC WHERE ((([06 Act 2005 Hosp - IM].POSITION)=1))) AS Q001 GROUP BY q001.code_esp_acc1, Iif([q001].[code_esp_acc1]="MG" Or [code_esp_acc1]="MF",1,0) HAVING (((Q001.code_esp_acc1) Is Not Null))]. AS Q002 GROUP BY Q002.GP;
... and this no longer works (syntax error in FROM statement).
To save you sifting through the SQL, the error is caused by Access replacing the soft brackets () aroung the outermost FROM statement with an object identifier-style hard bracket []. - specifically, the outermost SELECT statement now reads SELECT Q002.GP, Sum(Q002.Num_Calls) AS Total_Calls FROM [ instead of
SELECT Q002.GP, Sum(Q002.Num_Calls) AS Total_Calls FROM ( and the alias for the subquery reads ]. AS Q002 instead of ) AS Q002
Changing the Access alterations back to my original fixes this problem - so the problem is more an annoyance than a disaster. However, I have no idea why Access is doing this to my query, nor how to prevent it from doing so. Does anybody have any experience of this behaviour in Access and any ideas of how to fix it?
Several years ago, my colleague and I built some databases (using Access 97, if it matters). We are both responsible for the maintenance of them.
Recently, the hard drive in my PC crashed, and the IT group replaced it. Now, when I open one of the databases, I cannot see all of the Forms, Tables, etc. Thinking the database had been corrupted, I asked my colleague to open it on his machine. Everything was there, and can be viewed from his PC.
All was well until my hard drive was replaced. Has anyone seen this before? Any help will be greatly appreciated.
I have been using this export function for the longest time and today it is giving me an error:Object invalid or no longer set.
Code: On Error GoTo Err_cmdTest_Click 'Must 1st set a Reference to the Microsoft Office XX.X Object Library Dim dlgOpen As FileDialog Dim strExportPath As String Const conOBJECT_TO_EXPORT As String = "EXPORT"
I have a large spreadsheet with one column of information which is longer than what access accepts as long text. So when I convert the spreadsheet to access all the large boxes of information are shortened so that I am losing a lot of information.
I have been using this export function for the longest time and today it is giving me an error:
Object invalid or no longer set.
Export code:
Code: On Error GoTo Err_cmdTest_Click 'Must 1st set a Reference to the Microsoft Office XX.X Object Library Dim dlgOpen As FileDialog Dim strExportPath As String Const conOBJECT_TO_EXPORT As String = "EXPORT"
i wish to combine multiple fields (there are no fixed number of fields, they vary depending on the data, so i guess union queries are out of the question) into one large field. For example:
TABLE 1: PNumber PName C1 C2 C3
1 AAA 0.1 0.2 0.3
2 BBB 0.4 0.5 0.6
So i wish to combine the fields C1, C2 and C3 into a larger fields containing all the data. So considering the example above, it should look like this:
TABLE 2: PNumber PName C1+C2+C3
1 AAA 0.1
1 AAA 0.2
1 AAA 0.3
2 BBB 0.4
2 BBB 0.5
2 BBB 0.6
I plan on entering data into TABLE 1 using a form and running a query, or some code etc so that it looks like TABLE 2.
I am using an application that uses an Access database, and I believe the original database was created in the 97 version. The application queries the database and allows a person to view the data in the database to select a particular instrument to test. When the MS Office was upgraded by our IT we are no longer able to view the data in application. The fields, rows, and columns appear, but the numbers and letters are not visible. The data is really there, but can't be seen.
I have attached a screenshot of the view that shows the white columns and rows.
Hi all, I have an Access 2002 (SP3) frontend and SQL 2000 (SP3a) backend. 2 sites, 2 SQL servers ( identical hardware and software). Replication using Push Merge every 5 minutes.
10 users at 1 site and 15 at the other with Access 2002 frontend stored locally on each users PC.
I get random 'object invalid or no longer set' messages just appearing on the workstation screen without the operator doing anything. about 3 or 4 a week, but not on the same workstation at 1 of the sites only, but only during the working hours of the other site. We have out of hours users who never experience this issue.
Occasionally the error is preceeded by a SQL ODBC connectivity error but this rare.
I had been informed it was network issue but I have been running continuous ping from a suspect workstation to the sql server and back. When the error occurs there are NO dropouts in the pings.
It doesn't follow volume of data changes either as one site can make hundreds of changes and we don't get the error and then it can be after 10 changes the error occurs.
I have been researching this now for about 2 weeks to no avail, if anyone can point me in the direction of good resources you will make my year. :D
I'm using Access 2003 with a frontend/backend database on a network. Several of our forms have image controls with links to a folder of JPG images, and these have worked fine for months. I have followed the recommended linking method described here (http://www.access-programmers.co.uk/forums/showthread.php?t=92837&highlight=jpg+link) and as I mentioned everything has worked fine until recently. Foolish me decided to try out Office 2007 beta for a few days but uninstalled it after a few days. Now, on my computer only back on Access 2003, whenever I try to open one of the forms that previously worked I get an error message saying "Access does not support the format of the file '....jpg. Try converting the file to BMP or GIF format". Copies of the same frontend works fine on other work stations, and I can open jpgs in Word and Excel. Or, to be even more frustrating, an embedded jpg will show up fine in my copy of the program - it's only linked jpgs that are rejected. I first repaired Office 2003, then completed unintalled and reinstalled it - same error message. 2 hours with MS tech support has been likewise futile. I'm guessing it has to be some kind of registry error, but nothing I seem to do makes a difference. Any suggestions at all will be greatly appreciated, because my next step is a complete format and clean install of everything.
I need a field to permit only numbers. Upon selecting number for the data type I select Long Integer for a longer number... But it only allows 9 digits.. I need it to allow to enter in longer numbers... Any help? Thank you very much in avdance
Hi, I have been using the following query, literally for years, without any changes. I run it from code using db.execute, and I do use the dbfailonerror option.
One of the things it does is to add a '3' to the end of the [serial]. [serial] is the primary key in the TST3 table. You might think that there would be a problem if, say, I have a list of serials containing 35 56 1 13 and I'm trying to update them to 353 563 13 133 But this has worked OK in the past. NOW I'm getting a KV Error when it tries to update the 1 to 13, because there's already a 13 in the table.
Even stranger, when the query fails, all the rows BEFORE the offending record DO get updated. So the query fails, and I end up with: 353 563 1 13 (and yes, I DO have dbfailonerror set)
So, it looks to me as if update queries are no longer running as transactions. I am pretty certain that action queries have always been run transaction-wise in the past... if the query fails, the whole thing should fail. WHY is the transaction processing no longer working for this update query? Has anyone else noticed this?
I recently ran microsoft update and am now running Access 2002 (10.6771.6830) SP3.
I have made a form with a main form and two nested subforms. The main form and subforms display data to be edited - fields in which data can be added, but no new records need be added. I and two other users have used this form for about two months. Now when we go to enter data, we can still navigate the form but our changes to the fields in question are no longer saved.
Periodoically, the "Save Changes?" warning that pops when closing a query whose design has changed stops popping up, and the changes are automatically saved. I've tried going into the immediate window to change SetWarnings to True, but that doesn't fix the issue. (Further, I've scoured my code for instances of "DoCmd.SetWarnings False" to make sure that they are always followed by a "Docmd.SetWarnings True" in the Exit lines.
I've tried searching a number of different sights, and all I can find are instances of people talking about wanting to disable the Query Action warnings, which I don't want to do. I just want to be able to fiddle with queries, but then close them without saving in case I butcher something. I could create a copy of the query and fiddle with that, but I shouldn't have to...
I have a query which I have connected to an excel worksheet. The query displays as a table in excel. Recently I've decided to reorganise the data that goes into the query, however the query itself hasn't changed, there's a chain of queries all feeding data into each other to arrive at this final query, and it's further up the chain that I did the reorganising.
Now when I try to open the workbook, I get this error (the workbook is set to refresh data whenever it is opened)The query did not run, or the database table could not be opened.Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganised, then try the operation again.
If I try to edit the data connection or create a new connection, the query I've been using also no longer appears in the list of queries and tables I can get data from.The problem is, this wasn't the sort of reorganisation that I could do incrementally and then see what specifically caused the connection to fail. The query was out of action altogether until I'd finished the reorganising. So it's a bit complicated and I don't know what kind of things could cause a query not to be available to make a connection with Excel.
I am able to see from the list of queries I can connect to where the first query in the chain comes that I can't connect to. The only thing about this particular query worth mentioning is that it uses the Nz() function (it adds together sales figures by month from a list of sales, and in months where there are no sales, there is no data, hence null, when logically I want it to be zero, hence the use of the function).
In a current version of a MDB, clicking on a hyperlink in a table no longer connects to the internet (?!)
I know for certain that it used to, so I opened an older version of the MDB (from a backup file), clicking does connect... so somewhere along the way, I must have done "something" to the current file (and too much time has passed along the way to revert to the former...)
I have a lot of tables that need updating often, so I delete the old ones and re-import them then run the queries again. From time to time, I add / remove columns but in the case I removed columns that are NOT needed in the queries.
So, I ran the queries again and they run fine but it says "Enter Parameter Value" for a column that is no longer there. It's not in the SQL code either.
So where is it trying to get these columns from and how do I fix this?
