Recent content by Mari111

Thank you both, the COUNT function worked.

I have an Excel report that I am trying to re-create in MS Access with a SQL query. I have used a IIF SUM function but it is not returning the expected results ("YES") in particular fields, instead just returning blank cells. I have checked the SQL tables and based on the raw data, we should...

Hello, I have built an import button on a form which when clicked, transfers data from an excel spreadsheet (through file dialog picker) to a temp table. It then uses an append query to append the new data to a sql server linked table. It is working well with small volumes of data. However...

Hi, Found the code for closing the query and it works ok. Thanks arnelgp. Question solved. Thank you all.

Oh ok, it uses the temp table and appends the data in it to the linked sql server table....how do I close the qry in vba code? DoCmd.CloseQuery?

Hello, I have an import data button which creates a temp table in MS Access to import new data into and then runs an append query to add the data into a linked SQL Server table (the back end). I then need some code to delete the temp table, because it is re-created each time a user clicks...

Hi, I'm having trouble with my code to delete the temp table I've created. My code is DoCmd.Close acTable, "TblTemp_Table" DoCmd.DeleteObject acTable, "TblTemp_Table" It's recognising the DoCmd.Close command but not recognising the DoCmd.DeleteObject command line. It's throwing a...

Thank you Mark, I created an append query and it worked. Now I just need to find code for dropping the internal Access temp table so that it can be re-created next time the import button is clicked. We have our own data checking processes so I'm not too concerned about the data validation.

Hi MarkK, Thanks for your suggestion too. I've changed my code so to select only one file for the moment, instead of many files. My code is now: Private Sub cmdImportFilterResults_Click() Dim sExcelFile As String Dim fDialog As Office.FileDialog Dim varFile As Variant Set fDialog =...

Hi Ranman, Thanks that worked. However, Access created an Access table with the name I specified instead of appending the new data to the existing linked SQL Server table. How do I now transfer the data in the newly created Access table to the linked SQL Server table?

Hello, I have some Access VBA code behind a button in a front end MS Access 2010 to select excel files and import them (as new data) into a linked SQL Server table (the back end). Opening up the file selector dialog box is working well, but code throws a 2522 run time error 'Needs a file name...

So, I've managed to get the file picker dialog box to come up. Now it's throwing a run time 2522 error on the DoCmd.TransferSpreadsheet line: Can anyone correct this code?

Ok thanks. I did this but the same compile error comes up and it highlights the ExcelPicker() in the Access vba editor again. Where is it going wrong?

Ranman256, do you have any other suggestions? Your solution looks good although I don't see how it links to the sql server end? Thanks

Um, so I replaced ExcelPicker() with FilePicker() but the same error happened. Could you be clearer and write it out please? My current code is: Private Sub cmdImportFilterResults_Click() Dim sExcelFile As String sExcelFile = ExcelPicker() '' if sExcelFile is not blank then '' import the...