Hi all,
I'm having a bit of trouble with references. Several databases on my local network have begun to continually have trouble with he excel object reference.
Whenever one of these databases s started up I have to go to debug menu to refresh eh reference.
Can any tell me why this is and how i would go about fixing it?
I'm using access 97 and the version of the Excel object is 9
- Thanks in advance
-Elfman
The field type is "memo", the value is as following:
MELTING POINT: 166 C +/- 3 DEGREES C
MELT POINT SOFT PEARL TPU, T3080A BASE: 140 DEGREES C +/- 5
TPU-M9025 HARDNESS: 73-78 SHORE A MS5000C MELT POINT: 123 +/-5
TPU-M9025 MELTING POINT: 135 +/- 5 DEGREES C
8710 TPU T3080A MELT POINT: 144 +/- 5 DEGREES, NOT FOR BONDING
0.7MM FS-8088 MELT POINT: 138 DEGREES C +/- 5 DEGREES
After exported to Excel, some text (red font above) is missing, like below. If I copy to Excel, the same error.
MELTING POINT: 166 C +/- 3 DEGREES C
MELT POINT SOFT PEARL TPU, T3080A BASE: 140 DEGREES C +/- 5
TPU-M9025 HARDNESS: 73-78 SHORE A MS5000C MELT POINT: 123 +/-5
TPU-M9025 MELTING POINT: 135 +/- 5 DEGREES C
8710 TPU T3080A MELT POINT: 144 +/- 5 DE
How to fix this problem? Thanks!
The data is pasted in cell B1, but there are no column headings.
Code:
Dim rs As dao.Recordset
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set rs = CurrentDb.OpenRecordset("Name of my Query")
[Code] ....
If I copy the query manually and paste to Excel I get headings, so how should I edit this code to make it do that?
Secondly, I'll need to do a lot of formatting: set some column widths, wrap text, set borders etc. Theoretically I could export the data to a preformatted template, but the output could be anything from 10 rows to over 200, so I don't know how that could work.
I've got an Excel sheet with +700k rows and 20 columns that I wanted to import to Access. All fields are text except the field that I want to use as a primary key, but I planned to import that as a text as well.
When I used the import wizard, I set all fields to import as text except for three that I set to memo. The wizard didn't say there was any error after importing the data, but when I checked the table, I noticed there were *a lot* of records where many fields where blank. Some fields where completely unaffected by this problem throughout the entire table, but in the rest of them, there is data missing in many records, and when there is data missing, it is not always the same fields that are missing. I have been unable to find any pattern that explains why sometimes the records were imported correctly, and why sometimes they were not.
Hi all
This is an ongoing problem I have had for 4 weeks now.
I have made a a system thats acts like a clock In/clock out Out system.
the structure is somthing like this
ID
Username
tblDailyLog
TimeIn
MorningBreakOut
MorningBreakOut
LunchOut
LunchIn
AfternoonOut
AfternoonIn
TimeOut
All fields apart from ID (autonumber) and username (String*255) are Date field (there are a few others like DateOfTimesheet etc but they arnt important here)
When a user arrives in the morning they make a record which they use for the day
They then have a form with a whole bunch of buttons which simply updates the correct field. For example they click the "Sign in for the Day" button and it updates the correct field with the current time.
Everything was going fine until people noticed that every now and again a sign in time dissapeared.
I have hacked myself to death trying to solve this problem but still the updates go Astray.
Now each time a time is updated the process goes somthing like this
1. the user opens their timesheet for the day (the RS is SNAPSHOT and no locks)
2. User Hits a sign in/out button
3. The record source is changed to "" and all buttons hidden (to ensure the record isnt locked and to make sure you dont do two things at once)
3. The table is updated with the new time (using some dynamic SQL)
4. The table is repeatadly checked using a DO loop to make sure the the correct time went in.
5. when the returned time value of the field matches the varaible used to update it, the form is returned to normal and the user carries on his/her merry way (if it never matches the screen should crash but this never happens).
6. A New record is added to another table called "tblbugfixinglog" which records which field was updated and when. This is so that I have two records in two different ways (figured if one went astray I could pull it back off the other)
7. Another new record is added to yet another table called tblSQLRecord, which simply logs all .RUNSQL statements that are executed.
I thought that the two extra tables (and the check that the record had been updated) would help me track down where the records are going missing, but this isnt the case.
Now it appears that some records arnt being added to tblBugFixingLog and to tblSQLRecord either and some of these tables are getting quite a few #ERROR's in them..
None of the tables are related to any other and i've no idea how #ERROR lines are appearing in a table that has 1 function... to recieve new records ... no editing, no viewing, no deleting.
Does anyone have any idea how these updates/inserts can go missing or create #ERRORs.
I've built plenty of Databases in my time and have never come across this.
__________________________________________________ ______________
This is the function I use to add a record to tblBugfixingLog and tblSQLRecord
Private Sub AddBugLog(ByVal TimesheetNumber As Long, ByVal FieldUpdating As String, ByVal NewFieldValue)
Dim TempSQL As String
TempSQL = "INSERT INTO tblBugFixingLog (TimeAndDateOfEntrySERVER,TimeAndDateOfEntryPC,Fie ldUpdated,NewEntry,UserID,TimesheetNumber,Computer AssetNo) VALUES (" & _
"#" & Format(ServerGetTime(Environ$("LOGONSERVER"))) & "#," & _
"#" & Now & "#," & _
"'" & FieldUpdating & "'," & _
"'" & NewFieldValue & "'," & _
"'" & GetNTUser & "'," & _
"'" & TimesheetNumber & "'," & _
"'" & fOSMachineName & "')"
' MsgBox TempSQL
DoCmd.RunSQL "INSERT INTO tblSQLRecord (Username,DateAndTime,Screen,TheSQL) VALUES('" & LoginInfo.sUsername & "','" & CStr(Now) & "','Add Bug Log function','" & CleanData(TempSQL) & "')", False
'CleanData is a function that removes ' and " from the SQL string so i can easily add the SQL string into the table
DoCmd.RunSQL TempSQL, False
End Sub
Public Function CleanData(ByVal DataToClean As String)
Dim TempData As String
Dim i As Integer
TempData = ""
For i = 1 To Len(DataToClean)
Select Case Mid(DataToClean, i, 1)
Case "'"
TempData = TempData & "`"
Case """"
TempData = TempData & "`"
Case Else
TempData = TempData & Mid(DataToClean, i, 1)
End Select
Next i
CleanData = TempData
End Function
__________________________________________________ ____
I have no idea how this can create #ERROR lines in the table when it is just added to and nothing else.
Does anyone have any clue to what may be happening here.
(Oh yeah and no matter how hard I try, I can't replicate the problem.... works for me every time no matter how harse I am to it!)
Please save what little hair I have left and give me some hope
Cheers
Homer
I searched the archive and didn't find quite what I was looking for, so..
I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.
I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.
Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!
A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.
I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.
Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject
I have an excel file linked to a table in Access. Several fields are date data types in excel but are showing up as text fields in Access.
My real goal is to do a comparison between two tables, but only if the date of the one piece of data is newer than the other. I had planned on comparing the two date fields but even though I have formatted the date fields in my excel file to be "Date", when I look at the design view of my table it is showing up as "Text" and therefore I am unable to do this comparison.
I'm not sure if it's just something that I'm missing but maybe someone else knows an easy fix to this. I know this is probably a simple question, but I did search the forum and didn't find a thread that specifically dealt with this issue.
Thanks in advance for your help.
I am using Excel and Access 2010.
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
When I run this Query without the WHERE statement/Clause- it returns the accurate 985 records. However, when I include the criteria with the WHERE clause (it takes a range of +/- 10% of the Square Footage size into consideration), I lose 15 records, and only get 970 results. When I increase the range to +/- 15% in that field, it gives me 7 more records back.
Obviously it is dropping out records that do not meet the criteria for that field- how do I prevent that? I guess I would like it to display a ZERO or something, but still display the entire 985 records. What do I need to include to do that? Here's the SQL-
SELECT tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, Avg(tbSold.SoldPrice) AS AvgOfSoldPrice, Count(tbSold.SoldPrice) AS CountOfSoldPrice
FROM tbListing AS tbAct, tbListing AS tbSold
WHERE (((tbSold.SqFt) Between ([tbAct].[SqFt]+([tbAct].[SqFt]/100*10)) And ([tbAct].[SqFt]-([tbAct].[SqFt]/100*10))))
GROUP BY tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, tbSold.Status, tbSold.Area, tbSold.Level
HAVING (((tbAct.Status)="ACT") AND ((tbAct.City)="TEMPE") AND ((tbSold.Status)="CLOSD") AND ((tbSold.Area)=[tbAct].[Area]) AND ((tbSold.Level)=[tbAct].[Level]));
I hope someone can help me with this. I figure I'm missing one line of code but can't figure it out. I have 3 tables - tblGeneral (which is my main table), tblMDLink, and tblMD. The primary key in tbl General is FileNo. The primary key in tblMDLink is an autonumber and the primary key in tblMD is also an autonumber called MDID. tblMDLink is just a linking table containing the fields Fileno and MDID and uses an autonumber as its primary key. It contains some other fields that have nothing to do with this. I have a query called qryMDLink which contains all the fields of tblMDLink together with a calculated field calle full name which just combines the last name and first name fields of tblMD. Now I come to my form (which is actually a subfrom) called subfrmIMEDoctor. The purpose of the fomr is to allow users to add the names of various doctors to various files. The record source of the form is qryMDLink. The form also contains a FileNo field which is added programatically. The field has a drop down combo box with its record souce set to MDID. The drop down box has two columns with the first (not visible to the user) set to MDID and the second set to Full Name. Here's the problem - it won't let me enter data. Whenver I click on the box I get the message - "You cannot add or change a record because a related record is required in table tblIMEDoctor". By the way, I did set up one to many relationships beteen tblGeneral & tblIMELink and between tblIMEDoctor and tblIMELink. Any help would be greatly appresciated. Thanks,
Tom Gorton
i want to export a table to excel , open this file and execute a macro from another file.
the code i have now is :
Code:
DoCmd.OpenTable "Overzichtaanwezigheid", acViewNormal
DoCmd.RunCommand acCmdExportExcel
DoCmd.Close acTable, "Overzichtaanwezigheid"
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open ("C:UsersErwinDocumentsOverzichtaanwezigheid.xlsx")
XL.Visible = True
XL.Run "d: est.xlsm!Macro3"
Opening the excel file goes ok, running the macro however not.
I have a relatively large database 65M, 42K records.
For some reason I seemed to have lost most (not all) of the queries I have written. However, if I go to file>database properties>contents, I can see all my queries there.
I certainly did not delete them. Any I idea of what’s going on ? I'm, using Acess 2000 on win2K. Might this be a resources / memory related issue?
Thanks
Joe
Hi,
I have build a database which work perfectly on most computers. But one or two for the computer are error with the Left, Right and Date SQL function. Is it simplely miss for the install or it a problem with my code?
Thanks
I am running Access 2000. The control wizard is not available. It does not come up when I add a command button for example. How do I get it turned on?
View 6 Replies View RelatedHi,
when designing reports or forms, when I pressing the
toolbox icon, it would depress, but no toolbox was visible. I tried
resetting the toolbox (through view/toolbars) and I set all of the
settings back to default. I even uninstalled and reinstalled MS
Access several times but no luck.
Any help please.
Gunawan.
Recently I lost the the functionality of the toolbox then shortly after I lost the toolbox icon completely.
The only way to use the features was to create an new toolbar.
System Restore finally fixed the problem.
This has happened twice on me with the 200 and 2003 versions.
Is it me? and is there an easier way to sort this. Reinstalling Access never cured the problem either.
Ta
Houston, I need some answers.
I don’t have one of those MSysObjects. Now how do I get one and how do I populate it?
I have a database that has just been split. In the back end, I don't see the tables. I have gone to Tools>Options>View and found that Show Hidden Objects is checked. When I go to the front end, the links to the tables show up and I can open them and see the data. When I check the Linked Table Manager, I see that the path for the links is leading to the back end database.
I also noticed that on the bottom of the screen, the buttons for the front end show the name of the front end database and have blue page-like icons, but the button for the back end has only the pink key icon. Also, when I try to link another mdb to these tables, the window for selecting the table for linking is empty. I also notice that suddenly, any time I close an Access database, it goes through a compact and repair cycle. This just started happening today. I'm fairly sure that yesterday I could see the tables in the back end.
Oh, yes. Just remembered one other thing. Not everyone here has that location on the network mapped to the same drive. In order to accomodate everyone, the linked table manager was set up with the complete path to the folder:
\companyname.comfolderlevel1folderlevel2folder level3...ackend.mdb instead of
J:.....
Using Windows XP and Office 2003
There is no verticle scroll bar as was mentioned in another post I found when doing a search on "Hidden Objects".
Any ideas where I should try next?
Hi,
i've developed a program in access 2003, but now i'm on access 2000 and seems that one / more library is/are missing..
i've this part of code
Private Sub List0_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim strOrd As String, strSql As String
If Button = 1 Then
If Y <= 225 Then
If Right$(List0.RowSource, 5) = " Asc;" Then
strOrd = " Desc;"
Else
strOrd = " Asc;"
End If
strSql = "SELECT cliente.CLI_ID, cliente.CLI_TITOLO, cliente.Cognome, cliente.Nome, cliente.Indirizzo, cliente.Cap, cliente.[Citta'], cliente.Regione, cliente.Nazione, cliente.CLI_SOCIO FROM cliente WHERE (((cliente.CLI_SOCIO)=Yes)) ORDER BY "
Select Case X
and this is the screen with the libraries currently installed ..
http://img246.imageshack.us/img246/7383/libreriaum5.th.jpg (http://img246.imageshack.us/my.php?image=libreriaum5.jpg)
i was wondering which one is missing....i'm quite far from my laptop right now..
thanks in advance
bye
Alot of my databases entries are missing. today i went on MS access 2007 and it said "security risk" so i went to options and enabled marcros. but when i get on to my database there is only one entry! but i know the information is there. the file is 1700 mb. what should i do?
View 1 Replies View RelatedI make that ERD :-
http://www.imagehosting.com/out.php/i1365582_erd.PNG
is there any suggest to add or remove fields or tables ?
I attached the file.
and thanks.
I have a weird situation and I was wondering if anyone has had an issue with this. I have a record missing from a table. Normal Users are not allowed to delete records from the Form view. There is a blank record where the record should be. The auto number counts from 37, 38, 40. Record 39 is missing and there are blanks where it should be. The autonumber field is my Primary Key and it is blank just like the rest of the fields in the record. I know that the record existed at one time because one of the DB users has a report referencing that record. Any Ideas?
Thanks for the help,
Keith
I have what seems to be a fairly simple question.
There are two tables in my database. A and B. Each table has the same criteria: Name, Number, and Date.
I have created a formula in my query which will add the Number fields together when they both have the same name. My problem is that when there is not a row for a particular name on table B, the calculation does not list that row in the results.
I would like for my calculation to insert a zero in the space and add Number.
I have tried the Nz function, but this results in a missing name and date and just the numbers are shown.
How do I get the results to show a row where the name is only on one field and still make sure that it places a zero in its spot and add the zero and the other number together based on the name.
Thank you in advance for any assistance.
Shawn
I have these two queries with the following record count:
qryAzoogleGids has 188 records
qryCJGids has 202 records
qryAzoogleCJUnion displays 294 records and is a Union of the above two queries. So something is amiss! I am short on records, but why?
The database is attached.
Thanks,
Dave
I have a sequencial number in my data base. It counts 1,2,3 all the way to 302344. I want to find missing records. How would I come up with the missing records in a separte database. In other words if I had 1,2,4,5,8,9. I would want to have a database that would show me 3,6,7 since they are missing.
Bob
Query Design View is only showing Tables pane (top). Fields pane (bottom) is missing from previously saved queries. New queries show both panes as usual. Does any have any idea why? I cannot find any settings to show the bottom pane (or to hide it). Please help
View 3 Replies View Related