Check Database from Excel to Access 2010

[June7]

I don't follow the comment about 'his query'. The recommendation is to not use dynamic parameterized query.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Mohanss82]

I have managed prepare the search form.

I have splitted the Tables to BE, while the Dashboard form which is at FE retriving the data from couple of Linked excel files in the network drives. Now I see the this DB is having an exclusivity error in viewing the Dashboard form for the second user onwards. We have set the DB to open in the shared mode and still having this issue. I am at the final stages of this DB Project. Please suggest/help.

[June7]

Each user has their own copy of the frontend on their computer?

[Mohanss82]

I have placed the accdr file in the network. I copied using the shortcut of that file in each users desktop and been facing this issue... Is that what you meant or should I do something different ?

[June7]

Not a shortcut, I mean an actual copy of the frontend on their computer. I put it at the C: root of user computer. They can have a shortcut to their copy. The point is each user runs their OWN COPY of the frontend.

Think of an Access database as an application. Each user runs their own copies of applications installed on their computer - Excel, Word, PowerPoint, etc. Treat a split db the same way. User runs own copy of frontend like an application.

[Mohanss82]

SearchError.zip

Hi,

I am facing an "Run-time error '13' Type Mismatch issue on the below lines very frequently.

strWhere = strWhere & "([Cheque Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "

I observe this error now for the same parameter which worked fine for earlier search instances not sure why.... I tried removing the trailing AND operator in this line, but still ending with same error. Any suggestions pls ?

[June7]

Format() function results in a string value. A date is really a type of number entity.

Use # delimiter for date values.

strWhere = strWhere & "([Cheque Date] < #" & Format(Me.txtEndDate + 1, conJetDate) & "#) AND "

[Mohanss82]

Hi,

Thanks June. I have fixed the VB Part. I also need the same condition in SQL. where I will use this for Exporting results via DoCmd.TransferSpreadhseet. My SQL is below:

SELECT *
FROM Vault
WHERE (((Forms!frmClientSearch!txtChqNo) Is Null) Or ((Forms!frmClientSearch!txtContractNumber) Is Null))
Or
(((Forms!frmClientSearch!txtContractNumber) Is Null) And
((Vault.[Cheque No]) Like "*" & Forms!frmClientSearch!txtChqNo & "*")) Or
(((Vault.[Contract Number])Like "*" & Forms!frmClientSearch!txtContractNumber& "*") And
((Forms!frmClientSearch!txtStartDate) Is Null)) Or
(((Vault.[Cheque Date])>=Forms!frmClientSearch!txtStartDate) And
((Forms!frmClientSearch!txtEndDate) Is Null)) Or
(((Vault.[Cheque Date])<DateAdd("d",1,Forms!frmClientSearch!txtEndDate)) );

[June7]

That is SQL for an Access query object? Does the query work?

[Mohanss82]

No. It did not work.

[Mohanss82]

HI june,

SELECT *
FROM VaultWHERE (Vault.[Contract Number] Like "*" & [Forms]![frmClientSearch]![txtContractNumber] & "*") AND (([Forms]![frmClientSearch]![txtChqNo] Is Null)
OR (Vault.[Cheque No] Like "*" & [Forms]![frmClientSearch]![txtChqNo] & "*" ))
AND (([Forms]![frmClientSearch]![txtStartDate] Is Null)
OR (Vault.[Cheque Date] >= [Forms]![frmClientSearch]![txtStartDate]))
AND (([Forms]![frmClientSearch]![txtFilterEndDate] Is Null)
OR (Vault.[Cheque Date] <= [Forms]![frmClientSearch]![txtEndDate])));

Abve mentioned is the query and I am not able to find the error .Request to point the error.It would be greatful.

[June7]

I just looked more closely at the WHERE clause. I don't think it will work to have multiple references to the form controls with different parameters unless you use an IIf(). I don't use dynamic parameters in queries. I use VBA to conditionally construct WHERE clause and apply to form or report when opened. Review:
http://www.allenbrowne.com/ser-62.html

If you want to allow for the possibility that input will not be in every control, consider:

SELECT * FROM Vault WHERE [Contract Number] Like "*" & [Forms]![frmClientSearch]![txtContractNumber] & "*"
AND [Cheque No] Like "*" & [Forms]![frmClientSearch]![txtChqNo] & "*"
AND [Cheque Date] BETWEEN Nz([Forms]![frmClientSearch]![txtStartDate], #1/1/1900#) AND Nz([Forms]![frmClientSearch]![txtFilterEndDate], #12/31/2900#);

[Mohanss82]

Thanks June.

I fixed the above. Now I require 2 things as below.

1. In case of modifying the old data which are stored in rows having record id, I am trying to import the data to the BE table directly where the update should happen based on the value marked as "True" which is a Record Selector Field on display. How to write a Update Query to update the entire row based on the Record key if condition is marked as True.

2. I am done with exporting the results based on 2 fields which I search. I have set the export name of the file to be combination of 2 search parameters and current date and time. Now I require it to attach it to a email memo automatically on a click of a button. Any suggestions on VB for this ?

[June7]

1. Import from where? Can you set a link to the source?

2. consider SendObject method - no need to export data, this command can attach data in form of pdf or text file to email

[Mohanss82]

1. Its the Only way to enter data in to DB for now. There are 2 imports where one is for fresh data and the other one is to modify the existing data.

2. In this method, I doubt whether its supports XLSX files.