Solved: Access update Via Excel

[White_Nova]

Hi There

Wonder if anyone can help with this.

I have an Access Database with data inside it.

I would like to do the following:

In Excel cell(A1) i manually insert a start date, in Cell(A2) i manually insert a end date.

What i need is access to give me the data between these two dates???
Or to have a query in Access understand the two dates inserted by the user into Excel cell(A1,A2) and give me the queried data in Excel...

Is this possible and if so how?

Thanks in advance for your help!!!!
:bow: :bow: :bow: :bow: :bow: :bow: :bow:

[akn112]

So you want to import data from your excel sheet into an Access table, according to the two dates?

What i'd suggest is either linking or importing your excel spreadsheet into the database.
Create a form in Access where the user can input two dates. Create a query which takes the two dates and filters the data.

[White_Nova]

Sorry but there is a missunderstanding, i already have the data in Access in a table called Data
I want to extract the data from Access to Excel through two specific dates...

please also help me desypher this SQL Code into Access VBA Code

SELECT Data.TMCode, Count(Data.LeadNo) AS CountOfLeadNo
FROM Data
GROUP BY Data.SesDate, Data.TMCode
HAVING (((Data.SesDate) Between #11/1/2007# And #11/11/2007#) AND ("where ?Expr? [Data]![TMCode]"="J102"));

[Charlize]

White Nova, I already told you to use variables. Try it. And if you want to post in this section, put a link to the extensive post http://vbaexpress.com/forum/showthread.php?t=15987 at the excel forum. Use"xxxx" & range("Ax") & "xxxx" & range("Ax")

[White_Nova]

Hi Charlize

you following me??? ;-)

thanks for that, i have tried it and it works, the problem is the rest of the Select statement... i am generating the statement is access SQL and transfering it to Ecel VBA but it needs editing, i have tried combinations but nothing seems to work, it gives the following error:
Syntax error in date query expression...

Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _
sqlstr As String, dbfile As String, usernm As String, pword As String)
Set dbcon = New ADODB.Connection
dbcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile & ";", _
"", ""
Set dbrs = New ADODB.Recordset
dbrs.Open sqlstr, dbcon
End Sub


Public Sub searchpay()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim SQL As String
Dim filenm As String

SQL = "SELECT [Data].[TMCode], Count(Data.LeadNo) AS CountOfLeadNo FROM [Data]" & _
" GROUP BY [Data].[SesDate], [Data].[TMCode]" & _
" HAVING (((Data.SesDate) Between #" & Range("A2").Value & "# And #" & Range("A1").Value & ")" & _
" AND (Where [Data].[TMCode] = (" & Range("C3").Value & ""

filenm = (ActiveWorkbook.Path & "\Sales Telly Store.mdb")
Call GetCn(adoconn, adors, SQL, filenm, "", "")

Dim xlSht As Excel.Worksheet
Set xlSht = Sheets("Conf Details")
xlSht.Range("C9").CopyFromRecordset adors
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlSht = Nothing
End Sub

[akn112]

try copy+paste ur sql statement and running it in a test query from access. it should be easier to find the problem there

[White_Nova]

Hi There again,

I have a question - if my excel template is adding new records to access and i dont want to have duplicate records in the database (Ie - i add something and later need to change it and add it again to the database)

How would i edit my code below to do this?

Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\Russell\Desktop\Manager Program\DataStore.mdb;"
Set rs = New ADODB.Recordset
rs.Open "Store", cn, adOpenKeyset, adLockOptimistic, adCmdTable


Columns("A:B").Select
Selection.EntireColumn.Hidden = False



If Range("K1").Value = "Tues" Then

r = 5 'Starts at row specified
Do While (Range("C" & r).Formula) = "Tues"
' repeat until first empty cell in column A
With rs
.Add ' create a new record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value

' add more fields if necessary...
.update ' stores the new record
End With
r = r + 1 ' next row
Loop

Else

If Range("K1").Value = "Wed" Then

r = 9 'Starts at row specified
Do While (Range("C" & r).Formula) = "Wed"
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
.update ' stores the new record
End With
r = r + 1 ' next row
Loop

Else

If Range("K1").Value = "Thurs" Then

r = 13 'Starts at row specified
Do While (Range("C" & r).Formula) = "Thurs"
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
' add more fields if necessary...
.update ' stores the new record
End With
r = r + 1 ' next row
Loop

Else

If Range("K1").Value = "Frid" Then

r = 17 'Starts at row specified
Do While (Range("C" & r).Formula) = "Frid"
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
.update ' stores the new record
End With
r = r + 1 ' next row
Loop

Else

If Range("K1").Value = "Sat" Then

r = 21 'Starts at row specified
Do While (Range("C" & r).Formula) = "Sat"
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
' add more fields if necessary...
.update ' stores the new record
End With
r = r + 1 ' next row
Loop

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End If
End If
End If
End If
End If
Columns("A:B").Select
Selection.EntireColumn.Hidden = True
End Sub

[White_Nova]

The logic im trying to insert is as follows:

If record exists then
Update record
Else
If Records dont exits then
Addnew Record

[XLGibbs]

You can simplify that code you posted a little bit (for the addnew part)


Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\Russell\Desktop\Manager Program\DataStore.mdb;"
Set rs = New ADODB.Recordset
rs.Open "Store", cn, adOpenKeyset, adLockOptimistic, adCmdTable


Columns("A:B").Select
Selection.EntireColumn.Hidden = False

Select Case Range("k1").Value
Case "Tues"
r = 5
Case "Wed"
r = 9
Case "Thur"
r = 13
Case "Fri"
r = 17
Case "Sat"
r = 21
Case Else
GoTo ExitNow:

End Select


Do While (Range("C" & r).Formula) = "Tues"
' repeat until first empty cell in column A
With rs
.Add ' create a new record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value

' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop


ExitNow:

Columns("A:B").Select
Selection.EntireColumn.Hidden = True
End Sub

[XLGibbs]

What is the primary key or row level identifier for determining if the record already exists or not?

[XLGibbs]

Sorry but there is a missunderstanding, i already have the data in Access in a table called Data
I want to extract the data from Access to Excel through two specific dates...

please also help me desypher this SQL Code into Access VBA Code

SELECT Data.TMCode, Count(Data.LeadNo) AS CountOfLeadNo
FROM Data
GROUP BY Data.SesDate, Data.TMCode
HAVING (((Data.SesDate) Between #11/1/2007# And #11/11/2007#) AND ("where ?Expr? [Data]![TMCode]"="J102"));

Sub SQL()
Dim SQL As String
SQL = "SELECT Data.TMCode, Count(Data.LeadNo) As CountOfLeadNo"
SQL = SQL & "FROM Data "
SQL = SQL & " GROUP BY Data.SesDate, Data.TMCode "
SQL = SQL & " HAVING (((Data.SesDate) Between #11/1/2007# And #11/11/2007#) "
SQL = SQL & " And Data.LeadNo = " & Chr(34) & "J102" & Chr(34)

MsgBox SQL
End Sub

You would run this in Access using (where SQL is your string SQL statement, you can name it whatever


doCmd.RunSQL SQL

[White_Nova]

low level identifier is a concatenate between the week number, Agent Name, Day and Ses...

[Charlize]

low level identifier is a concatenate between the week number, Agent Name, Day and Ses...Those 4 fields give you a unique record ..., always ?