Problem with deleting all records from query table from Excel VBA

J

jaryszek

Registered User.
Local time
Today, 11:46
Joined
Aug 25, 2016
Messages
756
Hi Guys,

my code in Excel is looking like:

Code: 
Sub SQL_Baza_P()

Dim Connectstr As String
Dim HurtowniaADO As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim ZdanieSQL As String
Dim Login As String
Dim FileName As String
Dim Moja As New MyForm
Dim Lokalizacja_Pliku As String
Dim Lokalizacja_Folderu As String
Dim TimeEntry As String
Dim TicketNumber As String
Dim Wiersz As String
Dim NumerSpółki, User, CzasWpisu As String
Dim rsQuery As ADODB.Recordset
Dim NumerZgłoszenia As String
Dim AccessApp As Object

A_Wniosek.Range("Tabela_Wnioski").ListObject.ListColumns(15).DataBodyRange.Hyperlinks.Delete

'''Set AccessApp = CreateObject("Access.Application")
'''
Lokalizacja_Pliku = "\\nt-wro4-01\dane\IHRM\DOKUMENT\Pełnomocnictwa kadry i płace\Administrator\Baza Danych\Baza_Pełnomocnictwa.mdb"
Lokalizacja_Folderu = "\\nt-wro4-01\dane\IHRM\DOKUMENT\Pełnomocnictwa kadry i płace\Administrator\Baza Danych\"
'''
FileName = "'" & ThisWorkbook.FullName & "'[Excel 8.0;]"
'''
'''Login = Moja.Login()
''''Wiersz = Wpisy.Range("A1").CurrentRegion.Rows.Count
''''TicketNumber = Wpisy.Cells(Wiersz, 1)
'''''TimeEntry = Wpisy.Cells(Wiersz, 2)
'''''''Login = "ljar01"
'''''''TicketNumber = "5000020"
'''''''TimeEntry = "29-08-2016 14:33:33"
''''
''''Set Moja = Nothing
'''
Connectstr = "DSN=MS Access Database;DBQ=" & Lokalizacja_Pliku & ";DefaultDir=" & Lokalizacja_Folderu & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
'''
On Error Resume Next

HurtowniaADO.Open Connectstr

ZdanieSQL = "DELETE * FROM tb_razem"
HurtowniaADO.Execute ZdanieSQL

On Error GoTo Koniec

ZdanieSQL = "CREATE TABLE [tb_razem_all] ([Numer zgłoszenia] TEXT, [Numer spółki] TEXT, [Nazwa spółki] TEXT, [Rodzaj klienta] TEXT, [Numer pesel pełnomocnik] TEXT, [Nazwisko i imię pełnomocnika] TEXT, [Data obowiązywania od] DATE, [Data obowiązywania do] TEXT, [Zakres pełnomocnictwa] TEXT, [Rodzaj wniosku] TEXT, [Status wniosku] TEXT, [Osoba udzielająca pełnomocnictwo] TEXT, [Prawo udzielania dalszych pełnomocnictw] TEXT, [Status pełnomocnictwa] TEXT, [Link do pełnomocnictwa] TEXT, [Uwagi] TEXT, [Login] TEXT)"

HurtowniaADO.Execute ZdanieSQL

'ZdanieSQL = "INSERT INTO [tb_" & Login & "] SELECT * FROM [Zgłoszone Wnioski$] IN " & FileName & ""

ZdanieSQL = "INSERT INTO [tb_" & Login & "] SELECT * FROM [Zgłoszone Wnioski$] IN " & FileName & ""

HurtowniaADO.Execute ZdanieSQL

HurtowniaADO.Close
Set HurtowniaADO = Nothing

Columns("A:A").ColumnWidth = 15

MsgBox "Poprawnie odwołano zaznaczone numery zgłoszeń"

Exit Sub
Koniec:
Set HurtowniaADO = Nothing
MsgBox "Błąd" & Err.Description & vbCrLf & vbCrLf & "Nr błędu" & Err.Number, vbCritical, "Procedura ADO"

End Sub

tb_razem is my query table. I would like to delete all records in my query table, insert new ones from excel using VBA.

Why my code is not working?

Jacek Antek
 
"DELETE * FROM tb_razem" this is error number 2147467259...
 
Last edited:
Also you need to include the name of the other database in quotes
eg
ZdanieSQL = "INSERT INTO [tb_" & Login & "] SELECT * FROM [Zgłoszone Wnioski$] IN '" & FileName & "'"
 
Thank You Guys for your swift answers!

Hmm Object is open: adStateOpen.

Code:

HurtowniaADO.Open Connectstr

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase (Lokalizacja_Pliku)
objAccess.Visible = True

objAccess.response.Write (HurtowniaADO.State)
Click to expand...

Cronk,

problem is with this part of code:

ZdanieSQL = "DELETE * FROM [tb_razem]"
HurtowniaADO.Execute ZdanieSQL
Click to expand...

So tb_razem is a query. And problem is to delete records within this query.
If i have tb_razem_all as table there is no problem to delete record using this command above. So problem is in deleting records in QUERY table not Normal Table...

Work around is to have 2 databases. One from tb_razem query and second with tb_razem_all but i dont think so whether the solution is good...

Warm Regards.
Jacek Antek
 
If it isn't an updateable query you wont be able delete from it.
If it has joined tables, they need to be joined by primary key.
 
it is not an update, this is select table query:

SELECT * FROM tb_ljar01
UNION SELECT * FROM tb_ljar02;
Click to expand...

so tb_razem is a Union of all small tables.
uuu it is a pity that it wont be possible.

There is UNION function, no join.

Hmm so one solution is to create new table within database and try to move all data there. Hmm query table also get space from disc?

Or maybe create one more updatequery which would be updating the old query?

Jacek
 
I said it needs to be updateable. If you can't open the query and edit data in it it isn't updateable and therefore you can't delete records from it.

So yes you will need to find another way of deleting the records.
 
Since you are unioning all columns from two tables with no WHERE can't you just run the delete for each table?

delete * FROM tb_ljar01
delete * FROM tb_ljar02
 
Thank You static.

Hmm it is interesting idea but I will have about 10 - 15 tables (number of Team Leaders in my organization) so wrting all names of table would be a little bit confusing.

Furthermore, I have one big query table tb_razem and from this table each user downloaded specific data for him. So there is to many tables to do that.

I thought rather about creating one new table tb_razem_all.

So:
1. tb_razem query is joining whole little tables to big one query table
2. Administrator is downloading data from query table and inserting whole edited data to new normal table.
3. Users are downloading data from normal, updated table by Administrator.
4. Users send their data to little tables and again one process is beginning.

What do you think so about it?
 
Oh no there is a problem here...

When Administrator downloaded data from query table "tb_razem", made changes and upload data to tb_razem_all table.

But all users are uploaded data for tb_razem so administrator within his Excel will be having not updated data. Becvause updated are in tb_razem_all!

Help me please withi this i have no idea how to fix it,
Jacek
 
Moving and deleting data just bloats the database. Is it necessary?

Add a new field to the master 'query table' called 'UserID'.

Instead of 'downloading data from query table and inserting whole edited data to new normal table', add the users ID to the main table.

edit : and might fix above problem too.


As for running code multiple times, you can arrays to procedures..

Code: 
function ClearTables(param array tbls() as variant)
	for each t in tbls
		currentdb.execute "delete * from [" & t & "]"
	next
end function

ClearTables table1,table2,table3, ....
 
You shouldn't have a separate table for each team leader - this is poor design. You should store the team leader id in the record then you only have one table.

Edit - just noticed that Static also made this observation.
 
Hmm maybe as you said I should have only one big table?

But problem is that single Team Leader is doing changes within their Excel and after change a few records or simply add few records they are sending data to their specific table in Access.

So User1 has table tb_User1 because when is he changing data in Excel and give button "Send" his whole table is dropped in Access, next new table is created with edited and uploaded data from Excel.

And after all there is a query selecting all data and unioning updated tables from Team Leaders.
And from this big query table all data for each user is uptaded in real time.

So if i had one big table wih USER_ID ( i have a column with User ID now) i don't know how could i do this:

query table which unions all Team Leader tables looks like:

Bez_tytu_u.png


Now with this query table i have Login column with User Login as you can see.
So problem is how to update main query table?

static your function ClearTables is good and maybe this is a solution but i am afraid of speed my database.

Administrator will be doing changes within one big table in Excel, click "send button" and VBA will be opening Access application, drop table with specific ID User, and insert to it new edidted data.

Maybe this is solution. So after that only one table will be dropped with specific ID User where record was changed in Excel database.

Jacek
 
Why not get them to use Access instead of Excel...?
 
They have not unfortunately installed Access on theirs computers...;/
 
They don't need it, download the Access runtime - it's free :)
 
There is no point to asking why in my organization there is no need to install access. I have my database and i have to accomplish my goal.

So please let's back to main topic,

Jacek
 
Problem is in inserting code to main database:

ZdanieSQL = "INSERT INTO [tb_" & Login & "] SELECT * FROM [Zgłoszone Wnioski$] IN " & FileName & ""

HurtowniaADO.Execute ZdanieSQL
Click to expand...

Zdanie SQL =
"INSERT INTO [tb_ljar01] SELECT * FROM [Zgłoszone Wnioski$] IN 'C:\Users\ljar01\Desktop\Makro\Pełnomocnictwa\Admin\Administrator_Pełnomocnictwav4.xlsm'[Excel 8.0;]" and this is working.

but i don't know where can i add WHERE clause here. WHERE with reference to just have changed row in Excel table.
Can you help me please?

Jacek
 
They shouldn't need any version of Access installed just to view/edit data.

Data in Access > user interface in Excel
 
You must log in or register to reply here.

Similar threads

O
Problem to insert all data from report by id with where to other table and VBA query
Replies
14
Views
1,418
theDBguy
theDBguy
J
Refresh query from Excel VBA doesnt work
Replies
6
Views
1,626
jaryszek
J
1
Update Multiple Table Records With VBA Query
Replies
7
Views
960
pbaldy
pbaldy
A
Update a field in a table from a form with vba /problem
Replies
2
Views
837
Asghaar
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom