I would like to create a query where I can search my selected fields on a particular field. However I do not want to do this in the standard way. I would like the query to appear in a 'search engine' type format (like a form but with a search). Is this possible????
View RepliesI am trying to create a search facilty that will allow me to search any table /form.
I have read some posts and have tried to download some of the examples in the sample database section,however after downloading I can't open as my version of access (97) says it does not recognise the format.
Ant suggestions would be appreciated .
Thanks
Treggy
anyone knows how to make a search engine inside microsoft access or database? like for example, i want to look/find information on a specific record.. how will i start making a search engine? help me coz i dont know how.
View 10 Replies View Relatedhi..
I have tried to do a search on this but i cant seem to find something similiar. And I did a post of what i wanted to do here:
Click Me (http://www.access-programmers.co.uk/forums/showthread.php?t=74845)
This is my table structure:
http://www.geocities.com/gerald20000/Alpha/table.jpg
** I have keep this structure the simple. In actual of what i want to do, its more section under the 3rd lvl. as in more section under tblNAMe and tblRELATIONSHIP.
-> My structure:
tblQNA
CategoryNAME
CategoryMOOD
CategoryRELATIONSHIP
tblNAME
CategoryMALE
CategoryFEMALE
tblRELATIONSHIP
CategoryNEAR
CategoryFAR
tblMALE (Question related to Name - Male will be here)
MaleQuestion
MaleAnswer
tblFEMALE (Question related to Name - Female will be here)
FemaleQuestion
FemaleAnswer
tblMOOD (Question related to Mood will be here)
MoodQuestion
MoodAnswer
tblNEAR (Question related to Near Relation will be here)
NearQuestion
NearAnswer
tblFAR (Question related to Far Relation will be here)
FarQuestion
FarAnswer
So what am i trying to do? People would put in a question and an answer into a box. After that, the person can choose which category does the inputed question belongs to which category(male? female? .. ).This is actually a FAQ search engine.
So ppl will have an option to search for keywords and match questions. There should also be an option weather it should search from tblQNA or tblName or tblGOOD. So it has different level of searching. This is why it has such tree. So after searching, it will display the possible matched question (display question only). then the user can click which question to view the answer (together with the question).
Hope to get some advise. Is this how the best way to implement? OR is there a better method? pls advise and thanks in advance. ill be trying to do the access now. ill post as i goes along.
cheers
anyone knows how to make a search engine inside microsoft access or database? like for example, i want to look/find information on a specific record.. how will i start making a search engine? help me coz i dont know how. :confused:
View 2 Replies View RelatedHello everyone,
Here I am again all lost in the beyond side of Access. So, I would like to create a "search engine" , through a macros if possible, so that the users can find the one thing among the hundreds of specs available to them in this form/subforms assebly.
My form has two subforms inbuilt in it, hence it can be really time consuming to find the one piece of information they are looking for. And I was hoping that there was a way they could just type for example "rated voltage" in the search box and they would jump to the one textbox titled "rated voltage". Just like most of us do when reading a large document online, we just use "find" to get what we are looking for.
Thanks,
Hello,
I have a form with about 30 fields on it, all connected to a table with file information. I want to create a search form using all 30 fields, so that if a user inputs information in any one of these fields and clicks search, it will find records based on the combination of what he/she inputted in the fields. For all the fields that he/she leaves blank, the search engine will ignore in its search.
I have already created a query that does this somewhat. For each field, I have used this as the criteria:
IIf(IsNull([Forms]![File Search Form]![SearchFieldName])=False,[Forms]![File Search Form]![SearchFieldName],[FieldName])
I have put this in the criteria and they are all linked by an And statement. It works fine, except that the program does not seem to match Null fields together. So, if the user leaves a field blank, the search won't ignore that field, it will only show records with some piece of data in that field. All records that are Null in that field are cut out.
So, I guess my question is: how would I make the program be unbiased towards fields that are Null and let it include records that have null in the field? Am I going about this the wrong way?
Any way, hope it was clear. Thanks in advance. =)
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
I have included the form control [Forms]![AddMatterFrm]![MatterIdFld] in the criteria of the first of three queries.
1st Query - with the form control isolates transactions relative to the contents of the form variable (coming from an open Form)
The second query is a XTAB query further processing and summarising the transaction.
The 3rd one adds another expression field.
I execute the 3rd Query using the command below
Set rstDebtTrans3Qry = db.OpenRecordset("DebtTrans3Qry")
I then get the ....Database Engine does not recognise [Forms]![AddMatterFrm]![MatterIdFld] as a valid field name or expression.
If I take the control reference out and hard wire the value I want - there is no problem.
I'm sure it has something to do with the XTAB query but I need a solution.
I have an App that runs a few action queries using:
Code:
CurrentDb.Execute "[My Query Name]"
At some point I get this Error: The Microsoft Access database engine cannot find the input table or query <name>. Make sure it exists and that its name is spelled correctly. (Error 3078).The query is there, I can run it from the DB objects window.Queries run using CurrentDb.Execute earlier in the code.
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 Replies View RelatedDoes anyone know how I can check what vba engine I have installed in my machine. Thanks.
View 1 Replies View RelatedOkay - well, I have figured out how to edit the schema file but now I am getting a different error message when I try to do the export:
The Microsoft Jet Database engine could not find the object "Form888.txt". Make sure the object exists and that you spell its name and path name correctly.
I believe that it is spelled correctly and I have changed the path a few times to see if it was that and it's still not working. Can anyone help me with this please?
My 641 export works fine the other 2 do not. I get the above error messge.
Someone else set this up for me and I know very little about VB. I kinda just do like trial and error, you know?
Heres the code:
Private Sub cmdUpload_Click()
On Error GoTo Err_Upload_Click
DoCmd.TransferText acExportDelim, ExportSpec, EDMISfile, txtUpload
MsgBox txtUpload & " written."
DoCmd.Close (acForm), "frmedmisupload"
Exit Sub
Err_Upload_Click:
MsgBox Err.Description
End Sub
Private Sub comEDMIS_AfterUpdate()
On Error GoTo Err_EDMIS
EDMISfile = "qryForm641"
Select Case comEDMIS.Value
Case "Form 641"
ExportSpec = "Form641_Export_Specification"
EDMISfile = "qryForm641"
txtUpload = "\Server01dataEDMISForm641.txt"
txtBegClient.Enabled = True
txtEndClient.Enabled = True
EDMISfile = "qryForm641A"
Case "Form 641A"
DoCmd.SetWarnings (False)
DoCmd.OpenQuery "qryMakeTcounsel"
DoCmd.OpenQuery "qrycombine"
ExportSpec = "Form641A_Export_Specification"
EDMISfile = "qryForm641A"
txtUpload = "\Server01dataEDMISForm641A.txt"
txtBegClient.Enabled = True
txtEndClient.Enabled = True
DoCmd.SetWarnings (True)
EDMISfile = "qryForm888"
Case "Form 888"
txtUpload = "\Server01dataEDMISForm888.txt"
EDMISfile = "qryForm888"
ExportSpec = "Form888_Export_Specification"
txtBegClient.Enabled = True
txtEndClient.Enabled = True
End Select
txtUpload.Enabled = False
Exit Sub
Err_EDMIS:
MsgBox Err.Description
End Sub
Private Sub comEDMIS_BeforeUpdate(Cancel As Integer)
End Sub
Private Sub comEDMIS_Undo(Cancel As Integer)
End Sub
Private Sub Command6_Click()
End Sub
Private Sub Command9_Click()
MsgBox "Help Messages go here..."
End Sub
Private Sub Detail_Click()
End Sub
Private Sub Form_Load()
On Error GoTo Err_formopen
Dim begclient, endclient
begclient = DMin("[client Id]", "tblclientcontactinfo")
endclient = DMax("[client Id]", "tblclientcontactinfo")
txtBegClient = begclient
txtEndClient = endclient
BegClientID = txtBegClient.Value
EndClientID = txtEndClient.Value
txtUpload.Enabled = False
txtBegClient.Enabled = False
txtEndClient.Enabled = False
Exit Sub
Err_formopen:
MsgBox Err.Description
End Sub
Private Sub txtBegClient_AfterUpdate()
BegClientID = txtBegClient.Value
End Sub
Private Sub txtEndClient_AfterUpdate()
EndClientID = txtEndClient.Value
End Sub
Private Sub txtUpload_BeforeUpdate(Cancel As Integer)
End Sub
Hi,
We have been having intermittent problems with an MS Access 2000 front-end application linked to a SQL Server 2000 database recently.
From the switchboard a user was sometimes getting "There was an error executing the previous command". When she shut the application and opened it again it works fine for a little while - then the error occurs again.
I removed the generic error handling code from the code for the Switchboard form and I got the error message: "The Microsoft Jet database engine cannot find the input table or query 'Switchboard items'. Make sure it exists and that its name is spelled correctly."
I have been searching this forum and the web to find out what the cause of this error message is but I cannot find anything to go on or to try that might help get rid of this problem.
Does anyone know why this error occurs? Any idea how to stop it of happening?
Any ideas or suggestions would be greatly appreciated.
Start with: frm_W-GraphSearch
select from combo and ever this for date criteria: 12/1/06 to 12/13/06
Open report - There you will see one out of three graphs showing
Go to Report Design mode and open the sql in one of the graphs and try to run it, there you see the error:
"The Mocrosoft Jet engine does not recognize 'Forms![W-GraphSearch].Text0' as a valid field name or expression."
Anyone have any ideas?
Thanks again in advance for your help,
Kilch
Dear All,
I prepared times ago a database that contains important data that will be frequently updated.
Since yesterday I cannot load the database anymore. I get a pop-up with following statement:
Quote
The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time
Unquote
If I click ok, the loading process will be aborted.
I'm the only user of this database, neither the database nor the directory containing the database is sharable. It is located on the harddisk of my computer which nobody from outside can access.
What can I do to recover the access to my database?
I use MS Office 2003 but tried to open the database also on MS Office 2000 with the same result.
The Help function of MS-Access does not really help.
Who knows how to solve the probleem????
With regards
Siegfried
Hi,
I have a form that contains 3 required fields i.e. linked to other tables using an ID. If the user tries to close the form without entering data in the required fields I get the message:
"The Microsoft Jet database engine cannot find a record in the table 'table name' with key matching field(s) 'myID'
I have created an If Then Else statement to check if the required fields have been filled in, and if not display a MsgBox. the problem is that Jet database engine message pops up.
I've tried using DoCmd.SetWarnings False on the form but this doesn't stop them.
Any ideas?
Cheers
i get this error the time i enter info into a registration page from where i am getting all the customers info
Microsoft JET Database Engine error '80004005'
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
in line 74
and my corresponding query is
Set Commrs2 = Server.CreateObject("ADODB.Command")
Commrs2.ActiveConnection=strconnect
line 74: Commrs2.CommandText = "INSERT into registration(username,fname, mname, lname, sex, address, city, state, country, pincode, phone, mobile ,email)VALUES('"&logid&"','"&fname&"','"&mname&"','"&lname&"','"&sex&"','"&add&"','"&city&"','"&state&"','"&country&"','"&pin&"','"&phone&"','"&mobile&"','"&email&"' )"
Commrs2.Execute
Set Commrs3 = Server.CreateObject("ADODB.Command")
Commrs3.ActiveConnection=strconnect
line 79 : Commrs3.CommandText = "INSERT into userpass(username,password) VALUES('"&logid&"','"&pass&"'"
Commrs3.Execute
also sometimes i get this on line 79
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
i did a google search and i found this article
http://www.kbalertz.com/kb_884185.aspx
but i was not able to find the security warning dialogue box anywhere
i am using an access 2000 format database
any more info req plz let me know
thanks
I am trying to access a Microsoft Access database located on a server on my network from my web server. The folder containing the access database has been shared on the network with everyone having full access. I created a virtual directory on my web server pointing to the share on the other server and when I try to connect it says:
Microsoft JET Database Engine (0x80004005)
The Microsoft Jet database engine cannot open the file '\server1databasejt_test.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
The code to open look like:
Set objConn = Server.CreateObject("ADODB.Connection")
dbPath = server.MapPath("/Database/jt_test.mdb")
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
I have tried everything I can think of to get this to work and nothing works. Any ideas?
I am trying to make changes to a particular field in Access but whenever I try to do this, I get an error messsage saying that Microsoft Jet Database Engine Stopped the process because you and another user are attempting to change the same data at the same time.
There is no once accessing the database at this time and this error message appears only when I go to that specific field. I've also tried to delete the whole row but it wouldn't allow me to do that saying the program has been locked.
Any solutions? Thanks,
Rajiv
I have a recursive script that seems to timeout after a few dozen requests, the database is returning the typical "Microsoft JET Database Engineerror '80004005' " error ("Unspecified error").
The code is pretty straight forward, it is a function that calls itself to generate a tree structure for a site map. It works great the first few dozen times but seems to 'time out' and return the above error after a few dozen records (building the tree).
The code looks like this :
Function BuildContent(id,depth)
' open the children
Set GetKids = Server.CreateObject("ADODB.Recordset")
GetKids.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:usersXXXXhtmldb1.mdb"
GetKids.Source = "SELECT ID,Name FROM category WHERE parent = " & id & ""
GetKids.CursorType = 0
GetKids.CursorLocation = 2
GetKids.LockType = 1
GetKids.Open()
GetKids_numRows = 0
if GetKids.EOF <> true then
MyLinks = countlinks(id)
MyCats = countkids(id)
response.write "Number of Listings (" & GetKids("Name") & "): " & Mylinks & "<br>"
maxdepth = 1
BuildContent = "<ol>"
while GetKids.EOF <> true
KidsCats = countkids(id)
KidsLinks = countlinks(GetKids("id"))
if MyCats > 0 then show = true
if KidsCats > 0 then show = true
if depth < maxdepth then show = false
if KidsLinks > 0 then show = true
if MyLinks > 0 then show = true
if show = true then
BuildContent = BuildContent & "<li><a href='" & GetKids("id") & "_e.asp'>" & GetKids("name") & "</a></li>"
else
BuildContent = BuildContent & "<li>" & GetKids("name") & "</li>"
end if
if depth < maxdepth then BuildContent = BuildContent & BuildContent(GetKids("id"),depth+1)
GetKids.MoveNext
wend
GetKids.Close()
Set GetKids = Nothing
BuildContent = BuildContent & "<br></ol>"
end if
End Function
there are a couple of support fuctions :
Function countkids(id)
Set GetListing = Server.CreateObject("ADODB.Recordset")
GetListing.ActiveConnection = MM_Connection_STRING
GetListing.Source = "select * FROM category WHERE parent = " & id & ""
GetListing.CursorType = 1
GetListing.CursorLocation = 2
GetListing.LockType = 1
GetListing.Open()
GetListing_numRows = 0
countkids = GetListing.RecordCount
GetListing.Close
Set GetListing = Nothing
End Function
Function countlinks(id)
Set GetListing = Server.CreateObject("ADODB.Recordset")
GetListing.ActiveConnection = MM_Connection_STRING
GetListing.Source = "select * FROM records WHERE parent = " & id & ""
GetListing.CursorType = 1
GetListing.CursorLocation = 2
GetListing.LockType = 1
GetListing.Open()
GetListing_numRows = 0
countlinks = GetListing.RecordCount
GetListing.Close
Set GetListing = Nothing
End Function
I think the DB is being called too often, too quickly but I have no way of slowing the script down. If that's the case, is there a way to slow the code execution to give the DB time to catch up?
Otherwise, is it a driver problem (Microsoft JET Database Engineerror '80004005' ) and would switching to a OBDC connection give me better results or should I be using a different db driver?
Any help would be greatly appreciated.
I'm getting this error
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query _expression '123 street'.
The field in DB is type text. But the data to be written in the field is; number followed by characters. In other words it is an address field on the form, trying to insert new record into DB.
here is 3 different data I entered into field:
1. 'just some text' - works fine
2. 'text and number 123 ' - works fine
3. '123 number and text' - does not work. and gives the above error.
Anybody has any idea?
I have an annoying error that i cant get round... the error i receive is:
Insert into TSContacts (CompanyId,FirstName,LastName,ThereMessage,JobTitl e,UserName,[Password]) Values(0,'','','','','','')
Microsoft JET Database Engineerror '80004005'
Field 'TSContacts.FirstName' cannot be a zero-length string. /vip_admin/AddContact.asp, line 55
dont really know whats going on here....
this is the addcontact.asp code:
'execute insert command
Dim strUpdate
Dim strFirstName,StrLastName,strLoginName,strPassword, strMessage,strJobTitle
strFirstName ="'"&Request.Form("strFirstName")&"'"
strLastName ="'"&Request.Form("strLastName")&"'"
strLoginName ="'"&Request.Form("strUserName")&"'"
strPassword ="'"&Request.Form("strPassword")&"'"
strMessage ="'"&Request.Form("strMessage")&"'"
strJobTitle ="'"&Request.Form("strJobTitle")&"'"
nCompanyId =CDbl(Request.Form("strCompanyID"))
strUpdate="Insert into TSContacts (CompanyId,FirstName,LastName,ThereMessage,JobTitl e,UserName,[Password]) Values("
strUpdate=StrUpdate & nCompanyID
strUpdate=strUpdate & ","&strFirstName
strUpdate=strUpdate & ","&strLastName
strUpdate=strUpdate & ","& strMessage
strUpdate=strUpdate & ","& strJobTitle
strUpdate=strUpdate & ","& strLoginName
strUpdate=strUpdate & ","& strPassword &")"
Response.Write(strUpdate)
dbServer.Execute(strUpdate)
if err then
'if error stop
response.write err
else
Response.redirect "customercontacts.asp?CUSTID="&nCompanyID
end if
'dbServer.close
'dbserver=nothing
%>
<body>
<p> </p>
</body>
</html>
can anyone help - its been driving me up the wall!
thanks,
butain
I am having a problem running a make table query which is based on the same tables that I am using in a sub form.
Basically, the user selects certain records in the subform (which is in datasheet mode) using a check box, then once selected he hits a command button where there is some code that will run the make table query, which I use to filter other tables in a related reports.
However I keep getting this silly "database engine could not lock table" message relating to the sub form name. The subform is not linked to any records in the main form as the main form is unbound.
The solutions offered by putting DoEvents or Me.refresh/Me.requrey in the code do not work.
Hi All
My department has customer database using microsoft access with the main table being a linked table to SQL-Server database down in IT department.
I've only been recently made aware that the staffs have been having problem when trying to change or delete old data. It keeps on bringing up the error message
The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.
which is hardly the case since each staff are assigned only one customer to work on.
While it is technically possible to have many people working on the same data, business wise, it is impossible to do that.
I still come up with the same error even when the IT department confirms that no one accessing the SQL-Server table and I was the only one logged in.
This leads me to believe that there is something in the code that somehow preventing the table being edited or deleted. The problem is the database is so huge and was designed by my predecessor so I do not have any knowledge of the complete working of the codes behind it.
If it's the problem with the code, can anyone tell me what sort of codes caused this? If not, can anyone tell me what happened?
Any help would be most appreciated.
Thank you
Hello!
I am working on a database that uses a table from another access database. I am not sure but I think the problem started after this line was added to the code:
Set dbk = OpenDatabase("D:My documentsJapiOritaColorBazeKnjigovodstvo1.1.md b")
The database still works fine on one machine. When I copy it to another machine, that is when I receive 3197 error.
When I copy database to another machine without the code that contains the line from above, and copy the same code in the same form after opening copied databse, the problem is solved.
Does anybody know why is this happening? How can I fix this?
Thank!