Refresh query from Excel VBA doesnt work (1 Viewer)

[jaryszek]

Hi All,

in my Access database i have table and query for this table.
In Access everything is working very good.

But in VBA i can not run a Query - if I am using f8 (debugging button) there are working fine, but when I am starting whole macro the refresh query in access doesnt work.

Im am using ADODB.Connection.

My code is

Sub SQL_Baza()

Dim Connectstr As String
Dim HurtowniaADO As New ADODB.Connection
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 rs As ADODB.Recordset
Dim NumerSpółki, User, CzasWpisu As String
Dim rsQuery As ADODB.Recordset
Dim NumerZgłoszenia As String

// path of database

Lokalizacja_Pliku = "C:\Users\ljar01\Desktop\Makro\Pełnomocnictwa\Baza_Pełnomocnictwa.mdb"
Lokalizacja_Folderu = "C:\Users\ljar01\Desktop\Makro\Pełnomocnictwa\"

//name of Excel

FileName = "'" & ThisWorkbook.FullName & "'[Excel 8.0;]"

// login and ticker number which are added to table in Access.

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

// connection string

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

//open table
Set rs = New ADODB.Recordset

rs.Open "tb_wpisy", Connectstr, adOpenKeyset, adLockOptimistic, adCmdTable

/add new row to the table - this is working good!
With rs
    .AddNew
    ![NumerSpółki] = TicketNumber
    ![User] = Login
    .Update
    .Save
End With

/refresh query - [B]this is not working at all[/B]

docmd.openquery "qry_tb_wpisy", acViewNormal, acUpdate

// [B]this method i also have tried - with no success[/B]

Set rsQuery = New ADODB.Recordset
rsQuery.Open "qry_tb_wpisy", Connectstr, adOpenKeyset, adLockOptimistic, adCmdTable

With rsQuery
    .Update
    .Save
End With

Please help,
Jacek Antek

 

[JHB]

The marked parameter in the below line is wrong:

docmd.openquery "qry_tb_wpisy", acViewNormal[B][COLOR=Red], acUpdate[/COLOR][/B]

Here is a link to the accepted parameter:
https://msdn.microsoft.com/en-us/library/office/ff835947.aspx

 

[jaryszek]

wow ! Thank you ! It saves me a lot of time to fix this !

This is working now!

Jacek

 

[JHB]

You're welcome - good luck.

 

[jaryszek]

Hi,

i have unfortunately still the problem with this...
When I have in my code :

AccessApp.docmd.openquery "qry_tb_wpisy", 0, 1

the refreshing query is too slow to get the satisfying result...

Whe i put before this code "application.wait now() + timevalue("00:00:05")" it is working but as you can see - it is veeery slowly.
So query can't manage refreshing and i have not the latest record...

 

[jaryszek]

I have tried:

AccessApp.Visible = True
AccessApp.OpenCurrentDatabase (Lokalizacja_Pliku)
AccessApp.docmd.openquery "qry_tb_wpisy", 0, 1
AccessApp.docmd.runSQL ("UPDATE qry_tb_wpisy")

but still this is not working...

 

[jaryszek]

Ok,

this thread did the work:

http://stackoverflow.com/questions/20278363/this-action-will-cancel-a-pending-refresh-data-command