I've connected excel to SQL server.
when the connection is made in Queries & Connections with SQL language (No VBA), the excel table refreshes OK and non relevant rows disappear from the table with every hit on Refresh all button.
the problem with this method is that everyone can see the user and password as part of the connection string.
so I switch to VBA, put a password on the project. and creating all queries from there.
now the problem is with the updating of the table.
when hit refresh all, new data is going into the table OK, but the non relevant rows stay.
for example, I have to prepare an order, once finished it should disappear from the table and it's not.
the workaround is to first delete the content of that table and then move on with pulling the recordset.
the question is, is there a way to update table without first removing it's content?
because when I'm using VBA, the user cant move the headers to it's liking.