Appending records

B

BLUDEV

Registered User.
Local time
Yesterday, 23:30
Joined
May 1, 2012
Messages
20
Hello,

I am yet again in need of assistance. The goal I am trying to accomplish using VBA within Access 2010 is to append/add certain records to another database (Customer Service) from my database (Client Request). I tried using a macro to append then update records in my database but there is a "lag" time as to when the information is updated in the 'Customer Service' database causing my updates not to occur.

Here is the SQL from my query:
INSERT INTO Issues ( Customer, Title, [Due Date], [Opened By], [Opened Date], Priority, Comments, [Job Number], Description )
SELECT [TBL003_Combined Data].CUSTOMER, [TBL003_Combined Data].TITLE, [TBL003_Combined Data].[DUE DATE], [TBL003_Combined Data].[OPENED BY], [TBL003_Combined Data].[OPENED DATE], [TBL003_Combined Data].PRIORITY, [TBL003_Combined Data].[REF ID], [TBL003_Combined Data].[JOB NUMBER], [TBL003_Combined Data].[ISSUES DESCRIPTION]
FROM [TBL003_Combined Data]
GROUP BY [TBL003_Combined Data].CUSTOMER, [TBL003_Combined Data].TITLE, [TBL003_Combined Data].[DUE DATE], [TBL003_Combined Data].[OPENED BY], [TBL003_Combined Data].[OPENED DATE], [TBL003_Combined Data].PRIORITY, [TBL003_Combined Data].[REF ID], [TBL003_Combined Data].[JOB NUMBER], [TBL003_Combined Data].[ISSUES DESCRIPTION], [TBL003_Combined Data].UPLOADED
HAVING (((Count([TBL003_Combined Data].[REF ID]))>1) AND ((Count([TBL003_Combined Data].PRIORITY))>1));

and here is how I "thought" I could fix it through Access VBA:

Function UpdateIssuesDatabase()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
On Error GoTo UpdateIssuesDatabase_OpenError
con.Open _
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
"Dbq=\\MACSERV8\Company\Customer Service\Issues FE File\090314\Issues.accdb;" & _
"Exclusive=1;" & _
"Uid=admin;" & _
"Pwd=;"
On Error GoTo 0
With Access.DoCmd
.RunSQL "INSERT INTO Issues ( Customer, Title, [Due Date], [Opened By], [Opened Date], Priority, Comments, [Job Number], Description ) SELECT (CUSTOMER, TITLE, [DUE DATE],[OPENED BY], OPENED DATE], PRIORITY, [REF ID], [JOB NUMBER], [ISSUES DESCRIPTION])FROM [TBL003_Combined Data] WHERE Count([TBL003_Combined Data].[REF ID]))>1) AND ((Count([TBL003_Combined Data].PRIORITY))>1))"
con.Close
Debug.Print "Done."
Exit Function
UpdateIssuesDatabase_OpenError:
Debug.Print "Exclusive 'Open' failed. Quitting."
Exit Function
End With
End Function

I have highlighted where it is giving me the issue. Any assistance of what I need to do will be greatly appreciated.

BLUDEV....DIRE NEED
 
Thanks MarkK, "IN" totally slipped my mind. I will update my module and provide the outcome.
 
I am still running into a brick wall, I'm not an expert and need help as I keep getting a "Compile error" Syntax error for the following:

Function UpdateIssuesDatabase()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
On Error GoTo UpdateIssuesDatabase_OpenError
con.Open _
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
"Dbq=\\MACSERV8\Company\Customer Service\Issues FE File\090314\Issues.accdb;" & _
"Exclusive=1;" & _
"Uid=admin;" & _
"Pwd=;"
On Error GoTo 0
With Access.DoCmd
.RunSQL "INSERT INTO Issues (Customer, Title, [Due Date], [Opened By], [Opened Date], Priority, Comments, [Job Number], Description)" &_
"IN '\\MACSERV8\Company\Customer Service\Issues FE File\090314\Issues.accdb'" &_
"SELECT (CUSTOMER, TITLE, [DUE DATE],[OPENED BY], OPENED DATE], PRIORITY, [REF ID], [JOB NUMBER], [ISSUES DESCRIPTION])FROM [MS Access;DATABASE=" & Application.CurrentDb.Name & ";].[TBL003_Combined Data])" &_
 [Join]
"WHERE Count([TBL003_Combined Data].[REF ID]))>1)" & Count([TBL003_Combined Data].PRIORITY))>1)) & "';"
 End With
con.Close
Debug.Print "Done."
Exit Function
UpdateIssuesDatabase_OpenError:
Debug.Print "Exclusive 'Open' failed. Quitting."
Exit Function
End With
End Function

The red is what it has problems with; can anyone help
 
I don't know what your code is trying to do. I did run it through smart indenter for improved readability and made some changes to remove syntax errors.

Code: 
Function UpdateIssuesDatabase()
    Dim con As Object
    Set con = CreateObject("ADODB.Connection")
    On Error GoTo UpdateIssuesDatabase_OpenError
    con.Open _
            "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
            "Dbq=\\MACSERV8\Company\Customer Service\Issues FE File\090314\Issues.accdb;" & _
            "Exclusive=1;" & _
            "Uid=admin;" & _
            "Pwd=;"
    On Error GoTo 0
     Access.DoCmd.RunSQL "INSERT INTO Issues (Customer, Title, [Due Date], [Opened By], [Opened Date], Priority, Comments, [Job Number], Description)" & _
        "IN '\\MACSERV8\Company\Customer Service\Issues FE File\090314\Issues.accdb'" & _
        "SELECT (CUSTOMER, TITLE, [DUE DATE],[OPENED BY], OPENED DATE], PRIORITY, [REF ID], [JOB NUMBER], [ISSUES DESCRIPTION])FROM [MS Access;DATABASE=" & Application.CurrentDb.name & ";].[TBL003_Combined Data])" & _
        "WHERE Count([TBL003_Combined Data].[REF ID]))>1) and  " & Count([TBL003_Combined Data].Priority) > 1 & "';"
    
    con.Close
    Debug.Print "Done."
    Exit Function
UpdateIssuesDatabase_OpenError:
    Debug.Print "Exclusive 'Open' failed. Quitting."
    Exit Function
End With
End Function

but in doing so, I may have altered the logic/intent.

If you are doing any Access work, I suggest you get these 2 free utilities;
Smart Indenter
MZTools for VBA
 
You open a connection which you never use, which you can completely drop, and I'll hack out the error handling for clarity, and what's left is . . .
Code: 
Function UpdateIssuesDatabase()
    DoCmd.RunSQL "INSERT INTO Issues (Customer, Title, [Due Date], [Opened By], [Opened Date], Priority, Comments, [Job Number], Description)" & _
        "IN '\\MACSERV8\Company\Customer Service\Issues FE File\090314\Issues.accdb'" & _
        "SELECT (CUSTOMER, TITLE, [DUE DATE],[OPENED BY], OPENED DATE], PRIORITY, [REF ID], [JOB NUMBER], [ISSUES DESCRIPTION])FROM [MS Access;DATABASE=" & Application.CurrentDb.name & ";].[TBL003_Combined Data])" & _
        "WHERE Count([TBL003_Combined Data].[REF ID]))>1) and  " & Count([TBL003_Combined Data].Priority) > 1 & "';"
End Function
Your INSERT INTO looks fine, and what I'd do next is write a SELECT statement in the design grid that works. Then hack that SQL out of the designer and insert it here. Your FROM clause just needs the table name, not this fancy "CurrentDb.Name" concatenation, and simplify. Try to solve one problem at a time, so don't worry too much about your SELECT's WHERE clause till you get this thing working. Then get fancy.
Hope that helps,
 
jdraw said:
I don't know what your code is trying to do. I did run it through smart indenter for improved readability and made some changes to remove syntax errors.

Code: 
Function UpdateIssuesDatabase()
    Dim con As Object
    Set con = CreateObject("ADODB.Connection")
    On Error GoTo UpdateIssuesDatabase_OpenError
    con.Open _
            "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
            "Dbq=\\MACSERV8\Company\Customer Service\Issues FE File\090314\Issues.accdb;" & _
            "Exclusive=1;" & _
            "Uid=admin;" & _
            "Pwd=;"
    On Error GoTo 0
     Access.DoCmd.RunSQL "INSERT INTO Issues (Customer, Title, [Due Date], [Opened By], [Opened Date], Priority, Comments, [Job Number], Description)" & _
        "IN '\\MACSERV8\Company\Customer Service\Issues FE File\090314\Issues.accdb'" & _
        "SELECT (CUSTOMER, TITLE, [DUE DATE],[OPENED BY], OPENED DATE], PRIORITY, [REF ID], [JOB NUMBER], [ISSUES DESCRIPTION])FROM [MS Access;DATABASE=" & Application.CurrentDb.name & ";].[TBL003_Combined Data])" & _
        "WHERE Count([TBL003_Combined Data].[REF ID]))>1) and  " & Count([TBL003_Combined Data].Priority) > 1 & "';"
    
    con.Close
    Debug.Print "Done."
    Exit Function
UpdateIssuesDatabase_OpenError:
    Debug.Print "Exclusive 'Open' failed. Quitting."
    Exit Function
End With
End Function
but in doing so, I may have altered the logic/intent.

If you are doing any Access work, I suggest you get these 2 free utilities;
Smart Indenter
MZTools for VBA
Click to expand...

"Thank you for the tools mention, I will download these"
 
You're welcome.

?? Did you get your code working???
 
@ jdraw, still working on it but almost there. Will post the completed code I've finished testing. Thank you again.
 
I became so frustrated that instead of doing an "append" VBA code, I went ahead and did an append query which works just had to make a unique value table to only have the 1 record with the three items which needed to be added to the other databases table.

Thanks everyone.
 
You must log in or register to reply here.

Similar threads

M
Populate Sub Form with similar to last main form record data
Replies
6
Views
1,029
MHART08
M
A
Pre-filling certain fields on new record in form
Replies
9
Views
947
Gasman
Gasman
W
Converting IE access VBA code to work with Edge
Replies
4
Views
2,957
wackywoo105
W
Z
Assistance - Issue with Expression Function in SQL Query and Invoking Function in VBA Editor
Replies
4
Views
853
June7
June7
N
VBA Windows Socket Programming
Replies
0
Views
2,760
nector
N

Users who are viewing this thread

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