Excel dataconnetion to acces prevents me from saving to database

[MSKO]

Hello
i am using Excel for VBA in a userform and using excel as userinterface 8userform)
I have an acces database, frontend-DB and backend-DB, where data is stored.
I have made a dataconnection in excel wich gets all data from the backend-database to a sheet.
It is updated whenever i open the worksheet.
When i work with the data i my userform (VBA) i work with the data in the sheet.and i save data to the database (using the command: refresh.all)
Problem is that i cant save data to the database when i have the dataconnection in the workbook.
When i delete the data-connection in the workbook there is no problem saving to the database.
Does anyone know what i am missing?

 

[Joe4]

Welcome to the Board!

Updating Access from Excel an be a bit tricky, and usually requires some specific VBA code (I don't know if you already have that code or not, since you have not posted any code).
Typically, I try to avoid doing this whenever possible. If the purpose of Excel is solely for a data entry form, why not just use Access's Forms?
I think they are much easier to use, and if your data is already in Access, then everything would reside in Access and you wouldn't need to involve Excel.

 

[MSKO]

Thanks for the velcome and reply
You are right about using Access forms, but my strategy is to make the frontend database and then create a backend database.
When doing that i give every user a folder containing a frontend database and an Excell workbook.
in that way i can make changes in the workbooks without risk of lossing data in the database.

My dataconnection is not made programmaticaly in VBA, but selected in the builtin-connectionpossibilities as followa:

From the toolbar I choose “Data” >> “From Access” >> select backend database (when prompted) >> Select the table ( when prompted) >> “OK” >> when prompted import data select “Table” >> “ Start in exixting Workshee” >> aelect =$A$1 (first cell – first column). >> “Properties” >> “Enable background refresh” >> “Refresh data when opening the file”

Herefter i save by the following VBA-code:

Private Sub CommandButton_Save_Click()
Dim Databasepath As String
Databasepath = ThisWorkbook.Path & "\myDB.accdb"

Set cn = New ADODB.Connection
Set rec = New ADODB.Recordset
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Databasepath & ";Persist Security Info=False"
cn.Open
esql = "SELECT * from mytable"
rec.Open (esql), cn, adOpenDynamic, adLockOptimistic
rec.AddNew

rec("Date").Value = TextBox4.Text
rec("Company") = TextBox44.Text

rec.Update
rec.Close
Set rec = Nothing
cn.Close
Set cn = Nothing

ThisWorkbook.RefreshAll
End Sub

Does it make sense?

rRgards MSKO

 

[Joe4]

You are right about using Access forms, but my strategy is to make the frontend database and then create a backend database.

Note that you can do the same thing with Access, that is have a separate front-end and back-end.
Actually, that is the recommended "best practice" for Access databases, to split the database and give person their own copy of the Access front-end.