Bizarre Make Table Query Problem
Aug 25, 2007
I built my wife a database to use for her work that has worked fine for a good while now. Just yesterday, one of the make table queries began to act peculiar. Suddenly, whenever you try to run it it gives the following error:
"The Microsoft Jet database engine has stopped the process because you and another user are attempting to change the same data at the same time."
This is on a computer at our home so obviously no one else is trying to access it. I have checked and there are no other instances of the database running (I've even rebooted to be sure). I have checked when it was shut down and didn't see a locking file associated with the database. It is a single make table query feeding directly from another table.
As I said, it was working fine for a good while. I'm out of ideas so can anyone help????:confused:
View Replies
ADVERTISEMENT
Oct 4, 2007
Hi,
First time on here, so hello all!
I have a very strange request from a client who wants to do something simple that they can't because of poor database design (not mine!) . I can't think of an elegant solution so I'm wondering if anyone on here can?
They have multiple tables containing address information, for example they have one containing information about certain buildings. They can use the softcopy of the database to retrieve information about the building but for backup they now need a hardcopy.
Now part of the building information includes the alternative names for buildings (for example "City Museum" and "Art Muesum" might be different names for teh same building). The hard copy needs to have a line entry for both names (proper and alternative) and this is where the difficulty lies.
Using a simple example the table might be:
colour fruit
red apple
orange
yellow banana
The output I would need in the report is therefore:
Red (Apple)
Apple (Red)
Orange ()
Yellow (banana)
Banana (Yellow)
Now I may be being dense but I can't see how I can do this! All help very gratefully recieved :)
View 1 Replies
View Related
Jan 26, 2005
Hi all,
Been having this problem for a long time.
Everytime my function is being called, a new tuple gets inserted into Table "Stats" containing the below mentioned data.
sSQL = "SELECT * FROM Stats WHERE UserID = '"&sUserID&"'"
rs.Open sSQL,,,adCmdTable
'Save the data
rs.AddNew
rs("JaID") = lIdJa
rs("JsID") = lIdJs
rs("LanID") = lIdLan
rs("ResID") = lIdRes
rs("WsID") = lIdWs
rs("Date") = Date
rs("Time") = Time
rs("IP") = sIP
rs("UserID") = sUserID
rs.Update
Some of the weird data i got from Table "Stats"
1/25/2005 12:45:59 aaaa bbbb cccc dddd ..... row1
1/25/2005 12:46:24 aaaa bbbb cccc dddd ..... row2
1/25/2005 12:46:29 aaaa bbbb cccc dddd ..... row3
1/25/2005 12:46:35 aaaa bbbb cccc dddd ..... row4
1/25/2005 12:46:40 aaaa bbbb cccc dddd ..... row5
1/25/2005 12:46:40 aaaa bbbb cccc dddd ..... row6
1/25/2005 12:46:40 aaaa bbbb cccc dddd ..... row7
1/25/2005 12:52:51 aaaa bbbb cccc dddd ..... row8
Each time a person click on my site, one tuple will be inserted into the Table "Stats".
Time which is one of the column of the Table "Stats" is inserted into the table each time the function is called
The puzzling thing is how is it possible for the time to be the same for row 5,6 & 7.
This happens very often and i have totally no explantion for it. I tried changing the time format, date format, however i always get this.
Please advise.
Thanks a lot
View 2 Replies
View Related
Aug 29, 2006
Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
View 1 Replies
View Related
Jan 21, 2005
Hi everybody,
Beginner here needs help !
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
Thanks in advance !
View 1 Replies
View Related
Sep 23, 2007
Dear Access Expert
I wanted to know if it is possible to change the name of the Table which is going to be created using a Make-Table Query via code (VBA).
For example if my Make-Table query currently creates a table with the name "Table1" I want to change it to name "Table2" and then change it Back to "Table1" or "Table3" etc.... depending on the users selection.
View 6 Replies
View Related
Oct 14, 2013
How can you determine the name of a table that has/is going to be created by a make-table?
View 2 Replies
View Related
May 27, 2005
Hi All!
This to me seems absolutely bizzare! It happens in both Access 97 and Access 2003 so I know its not a version issue.
I have a Access database with a link table to a spreadsheet. I have written a query which displays data from this link table which filters out blank rows, the spreadsheet header etc and just leaves me with the data I want to import which first time I ran it was perfect and exactly what I wanted. However, it turns out that this query only works intermittently. If I run it subsequently I get a "numeric field overflow" error message. But if I go into the query design and resave it without making any changes it will then run again!!! Running it thereafter will bring back up the error message until I resave it again!!! Why is this? Any ideas anyone? I dont want to have to keep resaving it to run it.
Actually thinking about it unless I could of course resave it using code???
Regards,
Dalien51
View 3 Replies
View Related
Dec 22, 2005
When using a make table query, can you include an ID tag. in the output table? if so how
View 2 Replies
View Related
May 15, 2006
:confused: I am using a Make Table Query to filter a Linked Excel Table. Is there a way to cut/drop the first 8 characters of the text out of one of the fields as it creates the new table?
Field NameExcel DataFinal Data
Model_NameLATITUDE D600D600
Also, the final table has two Relationships with two other tables. When I run the Make Table Query once a week, I have to break the relationship to get it to run. Is there an easier way to dial with this?
View 2 Replies
View Related
May 19, 2006
Is there a way in a Make-Table query to tell the table to open when it's created? I would assume there is a function or SQL code that would do this, but I know very little SQL. Can anyone point me in the right direction? Thanks!
View 2 Replies
View Related
Aug 27, 2007
I have a database named NewUpdate.mdb. I have another database named MainDatabase.mdb. Almost all the tables in NewUpdate.mdb are linked tables that are in the MainDatabase.mdb file.
I've written a simple make-table query in NewUpdate.mdb that makes a backup of a table that is located in the MainDatabase.mdb file. (See code below.)
SELECT tblProviderRate.* INTO tblProviderRateSave
FROM tblProviderRate;
The only problem is that this new table is created in the NewUpdate.mdb file. I need the query to be stored in the NewUpdate.mdb file and the "new" table to be created/stored in the MainDatabase.mdb file.
This sound simple enough, but I'm drawing a blank as to what I need to do in order for this to occur. Can someone tell me what I'm leaving out??
Thanks,
CRhodus
View 2 Replies
View Related
Nov 26, 2007
How to make-table query to make a certain field a memo field instead of a text field? The reason I cannot use the text field is the limitation is 255 characters while I require 2000 characters?
View 4 Replies
View Related
May 28, 2006
Dear friend
I need your help desperatly. In my db, I am making a "make table query" to match some text.
Help me build this please.
First a form which has text box, where I will put in some words..
Then a make table query, which will have =[form]![name of form]![control name]
A report on same table.
Means when some one puts some words in text field of form, on pressing hit button, a like statement is automatically created in one field of query which matches those words and a table is generated from them. Result is report.
Hope this is clear, could you please help me build this?
View 14 Replies
View Related
Dec 7, 2006
How can I set a button in an MSExcel sheet to run MSAccess make table query?
Please help. Thanks.
View 2 Replies
View Related
May 31, 2005
I've got a problem with a make-table query that creates a new table which is then populated with new values that relate back to the original table the trouble is the make-table won't run with that relationship in place.
Is there a way to delete the relationship then reinstate it after the query? or any other way round that someone can think of.
Thank You
View 1 Replies
View Related
Jul 31, 2007
I have come across a snag in my access project I am currently undertaking and need some advise on what should be done next. . .
In a nutshell, this project's primary purpose is to perform a search on a table of information based on many fields and generate a report based on the records which match the search. I have the search form working (similar to this one here) http://allenbrowne.com/ser-62.html
It displays the results from the table based on the search criteria. The form is great for viewing the results which match search criteria; however, I am having trouble doing anything else with the data (perform operations on the data, send the results to the table, etc. . .). How should I go about moving this data to a table?:confused:
View 3 Replies
View Related
Aug 20, 2007
I have used/am using the "INTO tbl_temp_copy" to create a temp table from a query called tbl_temp_comp. Everytime I run my query it deletes and then creates a new table with the same name - and this is what I have been wanting.
However now I would like to create a table with the date in the table name so:- "INTO tbl_temp_copy_Date()" but obviously this does not work and I do not know if it is even possible?
Anyone able to do this or know that it cannot be done?
View 1 Replies
View Related
May 25, 2005
Greetings all,
I'll try to make this as simple as possible, but please ask for clarification if I'm unclear:
*I have one pass-through query to Informix that pulls all available class schedule information.
*I have another local query that uses information in a local table to restrict that query to a particular year and semester, chosen by the user.
*I have a function TimeConv that takes the numeric value used in the Informix db to store times (e.g., "1300") and converts it into a real time value (e.g., "1:00:00 PM").
The problem is this:
While the TimeConv function by itself always works in the local query, if I try to perform any comparisons on it, I get the error message "Invalid Use of Null". There are no null records in the restricted data. If I put the semester and year restriction directly into the pass-through query instead of the local query, the comparison operations work just fine. (But it's important that the user be able to change this without editing the SQL directly.) Only when that restriction is in the local query do I get the error message.
I've tried several ways of filtering out Null values from both queries, and it doesn't seem to make any difference. If I Nz() the fields before passing them to the TimeConv function, I instead get the error "Invalid procedure or argument call", I believe because it insists on reading "0000" as "0", which is too few digits for the function to work.
I get the same results if I try to run it through yet a third (local) query.
Relevant code and SQL below. I'm really stumped on this one. Thanks in advance for any suggestions!
Pass-through query (qryPassJoin1):
SELECT t3.days, t3.room, t2.crs_no, t3.bldg, t3.mtg_no, t1.sec_no, t3.beg_date,
t3.end_date, t3.beg_tm, t3.end_tm, t9.sex, t5.abbr_name, t1.yr, t1.sess
FROM informix.sec_rec t1, informix.crs_rec t2, informix.acad_cal_rec t7,
outer (informix.secmtg_rec t8, outer informix.mtg_rec t3), outer
informix.dept_table t6, outer informix.schd_comment_rec t4, outer
(informix.fac_rec t5, outer informix.profile_rec t9)
WHERE t1.cat="UG93" and t1.crs_no=t2.crs_no and t1.cat=t2.cat and
t1.crs_no=t8.crs_no and t1.cat=t8.cat and t1.yr=t8.yr and t1.sess=t8.sess and
t1.sec_no=t8.sec_no and t8.mtg_no=t3.mtg_no and t1.crs_no=t4.crs_no and t1.cat=t4.cat
and t1.yr=t4.yr and t1.sess=t4.sess and t1.sec_no=t4.sec_no and t1.fac_id=t5.id
and t2.crs_no NOT IN
("JFESSA", "FRESSA", "SOESSA", "JRESSA", "MAESSA", "MAORAL", "DEPO", "DEPO2", "SRESSA", "SRORAL")
and t2.dept=t6.dept and t7.prog=t2.prog and t7.sess=t1.sess and t7.yr=t1.yr
and t5.id=t9.id;
If I add this criteria, functionally equivalent to the first WHERE statement in the local query below, it all works fine:
AND t1.yr=2005 AND t1.sess="SU"
Local Query (qryCatalog):
SELECT qpj1.days, qpj1.room, qpj1.crs_no, qpj1.bldg, qpj1.mtg_no, qpj1.sec_no,
qpj1.beg_date, qpj1.end_date, qpj1.beg_tm, qpj1.end_tm, qpj1.sex,
qpj1.abbr_name, DateDiff('h',TimeConv([beg_tm]),TimeConv([end_tm])) AS NoLongLab
FROM qryPassJoin1 AS qpj1, tblSettings
WHERE (((qpj1.beg_tm)>0) AND ((CInt([yr]))=CInt([tblSettings].[catyear])) AND
((qpj1.sess)=[tblsettings].[catsess]));
The comparison criterion I would like to add here is:
AND ((DateDiff('h',TimeConv([beg_tm]),TimeConv([end_tm])))<=2)
Function TimeConv:
Public Function timeconv(numtime As Variant) As Date
timeconv = CDate(Left([numtime], (Len([numtime]) - 2)) & ":" & Right([numtime], 2))
End Function
View 3 Replies
View Related
Oct 31, 2005
How can I get a make-table query to run in a report?
View 6 Replies
View Related
Apr 19, 2007
I am having trouble with a make-table query. I run a query that populates my website with data daily. I have a bunch of fields in the db that I wanted to be displayed as one field on the website, so in my query I concatenated them. My website is able to distinguish which fields are text and which are memos so I can display multiple lines. Everytime I run the query, it creates the concatenated field as a text field. Is there a way I can default this to a memo type?
Thanks.
View 1 Replies
View Related
Aug 24, 2007
I want to make a table from the results of a crosstab query and am struggling to find a way. Is this possible?
Any help gratefully received!
View 9 Replies
View Related
Mar 24, 2008
Hi,
Anyone has the idea of creating primary key in a table using make table query?
Thanks.:confused:
View 1 Replies
View Related
Sep 22, 2005
I am creating a table with a make-table query. The only trouble is that I can't figure out how to designate which field will be the primary key. Is there a way to do this...... other than creating the table, then manually going into desing mode and specifying the primary key there? I want this to be automated.
Thanks,
David
View 3 Replies
View Related
Dec 4, 2006
Hi,
Quick question: I am running a union query via a recordset.
How can I store the results in a new not yet defined table?
Cheers,
Gurkentopf
View 4 Replies
View Related
Jan 30, 2006
I have a form that opened perfectly before from a click event with a standard link criteria lookup function, and...it's suddenly stopped working!
Before the form opens I get a small query box asking: Forms!support!fault ref
In other words it's asking me to enter the value it should've got from the link criteria lookup. Whatever I enter, the form then loads with the correct record.
Is there a known Access corruption issue, or does anyone have any ideas?
View 1 Replies
View Related
