Access database won't open after closing it via Excel VBA

[mp3909]

Hi,

I have this piece of excel vba code that creates a csv file and then opens up an Access database which imports that csv file into a table.
Here is the code that imports the file into Access (all done in Excel VBA).

Function openAccessDb()

Dim accessObj As Access.Application
Set accessObj = New Access.Application
accessObj.OpenCurrentDatabase DBFile
accessObj.Visible = True


Dim oConn As ADODB.Connection
'Dim rs As ADODB.Recordset


Set oConn = New ADODB.Connection
'Set rs = New ADODB.Recordset


Dim conStr As String
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\mfil\proddfs\AppSense\Profiles\Users\patma01\Desktop\Database21.accdb;"
oConn.Open conStr


oConn.Execute "DELETE * FROM [Data dump]"
oConn.Close
Set oConn = Nothing


accessObj.DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="spec", TableName:="Data dump", Filename:=fPath & fName, HasFieldNames:=True
accessObj.CloseCurrentDatabase
accessObj.Quit acQuitSaveAll


End Function

The issue is that after the macro has finished running, when I try to open the database I get this error:

"MICROSOFT ACCESS HAS STOPPED WORKING".


The funny thing is that if I do the whole import into access manually without VBA, it works fine.
I suspect it has something to do with the way Excel VBA is closing the Access appplication.

Anyone experienced similar issues?



Thanks

[ranman256]

Can’t you run the import in access?

[mp3909]

I can but the reason why I do not want to do in Access is because the data the end user is working on is in this excel spreadsheet and the final submission of the report has to be in an excel format.
The data I am trying to import is in that workbook the user does their work in so I have created an excel add in.
One of the task is to add an extra column to that data which determines what sort of product it is and this is based on rules. I was planning to accomplish this in excel vba without using access but it would end up being a for loop and the data size is like 170,000 rows and for determining the product type you need to check 5 different fields so I thought let me import data into access and accomplish this via writing a sql query as it would be much quicker and then export the data back into the same spreadsheet.

[ranman256]

what about this:

Function openAccessDb()
Dim accessObj As Access.Application
Dim conStr As String


Set accessObj = New Access.Application

with accessObj
.OpenCurrentDatabase DBFile
.Visible = True


.DoCmd.setWarnings false
.DoCmd.RUNSQL "DELETE * FROM [Data dump]"


.DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="spec", TableName:="Data dump", Filename:=fPath & fName, HasFieldNames:=True
.Quit 
end with

set acccessObj = nothing
end sub

[mp3909]

thanks but still get the same issue. Code executes fine. But when I try to manually open the access db i get the message "Microsoft Access has stopped working" and then I permanently see a locked version of the file appear as well.

[Gicu]

How about this version:

Function openAccessDb(DBFile as String) 
'added file name as argument, you probably have as a global variable

Dim accessObj As Access.Application
Dim sSQL as String


Set accessObj = New Access.Application
accessObj.OpenCurrentDatabase DBFile
accessObj.Visible = False


sSQL="DELETE * FROM [Data dump]"
accessObj.CurrentDb.Execute sSQL,dbFailOnError


accessObj.DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="spec", TableName:="[Data dump]", Filename:=fPath & fName, HasFieldNames:=True
accessObj.CloseCurrentDatabase
accessObj.Quit acQuitSaveAll


Set accessObj=Nothing


End Function

If you open the task manager after you run the function (but before attempting to open the Access db), do you see a running process for Access?
Cheers,
Vlad

[mp3909]

I tried this code as well.
Executes perfectly but when I try to open the db file manually after execution, get the same error message "Microsoft Access has stopped working" and I also see a locked version of this file appear even when I close the access application.

I do not see a running process for Access in task manager after I have run the function (but before the code line accessObj.OpenCurrentDatabase DBFile).

Thank You

[Gicu]

Where is the Access db located? Local drive or network? If network can you try to move it to a local folder where you have full (read\write) permissions? You said you can run it manually without any problems which I assume involves opening Access and going to the External Data tab on the ribbon and selecting Excel Import.... Is that what you meant?
As for the running process I meant after you run the full function before you manually trying to open Access. How do you get rid of the error after it happens to try again? Maybe your Access db got corrupted so I would suggest running a full decompile\compile on it:
http://www.fmsinc.com/microsoftacces.../decompile.asp
https://www.barnesbusinesssolutions....ssDatabase.pdf

Cheers,

[mp3909]

The access file is located on my desktop.
It is a very small file with just one table only (the table where the data gets imported to).
Nothing else is in that Access file.
Each time I get this error, I have to delete the file and recreate a new file (with new spec for the import) and when I run the function targeting the new file, still same issue.
No access is running after the function has finished.

Thanks

[Gicu]

Have you tried to create an autoexec macro in the Access file to run the code on open (then close itself)? It must be something with your import spec....

Cheers,

[mp3909]

I am not sure how to go about doing this using autoexec macro - will that not deviate away from building the solution in Excel?
I want to keep all my code in excel.

I have attached a sample of the data.
When I manually import this data into Access, I make sure fields 4 and 5 are changed from Long Integer to Text.
Please see if anyone is able to import this data into Access using Excel VBA.

Thank You

[Gicu]

Have a look at the attached files. Save them in the same folder and click the button in Excel.

Cheers,

[mp3909]

Hi Vlad,

Appreciate your solution and time.
It works well for the sample data size I had given but in reality, the actual data set has 173,000 records and when executing the macro against this, I get "Microsoft Access has stopped working" after the import has completed and it shuts it self down and re-opens automatically (compacting database can been seen written on the bottom right hand corner of the screen) without any issues and the new data can be seen. So it works but its just annoying the end user has to see that error message, watch Access close itself and re-open again (which takes a few mins).

Thanks

[Gicu]

Try removing the Compact On Close from the Access database (in File\Options\CurrentDatabase).

Cheers,