mercmannick
07-09-2006, 10:10 AM
Sub update_data()
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset, r As Long
'additional declarations to suit new syntax
Dim MyConn
Dim sSQL As String
Dim i As Integer
sSQL = "SELECT MRP_CODE.CELL, Count(IMFINTERNAL.[Short Mat]) AS [CountOfShort Mat] " _
& "FROM IMFINTERNAL INNER JOIN MRP_CODE ON IMFINTERNAL.[Short MRP] = MRP_CODE.MRP_CODE " _
& "WHERE (((IMFINTERNAL.[Days Late]) > 0)) " _
& "GROUP BY MRP_CODE.CELL;"
' connect to the Access database
' and axx of IMFINTERNAL_BY_CELL qry
Set Cn = New ADODB.Connection
MyConn = "C:\db1.mdb"
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
.Execute sSQL
End With
Sheets("Data").Activate
spe = Cells(Rows.Count, 3).End(xlUp).Row ' the next row in the worksheet for speed cell
E = Cells(Rows.Count, 7).End(xlUp).Row ' the next row in the worksheet for cell e
F = Cells(Rows.Count, 11).End(xlUp).Row ' the next row in the worksheet for cell f
G = Cells(Rows.Count, 15).End(xlUp).Row ' the next row in the worksheet for cell g
W = Cells(Rows.Count, 19).End(xlUp).Row ' the next row in the worksheet for cell w
S15 = Cells(Rows.Count, 23).End(xlUp).Row ' the next row in the worksheet for s15
S70 = Cells(Rows.Count, 27).End(xlUp).Row ' the next row in the worksheet for s70
S17 = Cells(Rows.Count, 31).End(xlUp).Row ' the next row in the worksheet for s17
End Sub
'THIS IS WHERE I NEED HELP THE QUERY ABOVE OUTPUTS LIKE THIS
'Cell F 98
'Cell G 6
'S70 28
'Speed 20
'IS THERE A WAY OF FROM QUERY ABOVE TO PUT THE VALUES ie
'CELL F SHOULD GOTO F = Cells(Rows.Count, 11).End(xlUp).Row
'Cell G SHOULD GOTO G = Cells(Rows.Count, 15).End(xlUp).Row
'ETC...........BUT IF THERE IS NO OUTPUT ON A SPECIFIC CELL PUT A 0 IN SO NEXT TIME RUN all the rows match in line
'THEY WILL ALL BE ON SAME ROWS
also posted on mr excel http://www.mrexcel.com/board2/viewtopic.php?t=221276 (http://http//www.mrexcel.com/board2/viewtopic.php?t=221276)
thanks
Merc
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset, r As Long
'additional declarations to suit new syntax
Dim MyConn
Dim sSQL As String
Dim i As Integer
sSQL = "SELECT MRP_CODE.CELL, Count(IMFINTERNAL.[Short Mat]) AS [CountOfShort Mat] " _
& "FROM IMFINTERNAL INNER JOIN MRP_CODE ON IMFINTERNAL.[Short MRP] = MRP_CODE.MRP_CODE " _
& "WHERE (((IMFINTERNAL.[Days Late]) > 0)) " _
& "GROUP BY MRP_CODE.CELL;"
' connect to the Access database
' and axx of IMFINTERNAL_BY_CELL qry
Set Cn = New ADODB.Connection
MyConn = "C:\db1.mdb"
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
.Execute sSQL
End With
Sheets("Data").Activate
spe = Cells(Rows.Count, 3).End(xlUp).Row ' the next row in the worksheet for speed cell
E = Cells(Rows.Count, 7).End(xlUp).Row ' the next row in the worksheet for cell e
F = Cells(Rows.Count, 11).End(xlUp).Row ' the next row in the worksheet for cell f
G = Cells(Rows.Count, 15).End(xlUp).Row ' the next row in the worksheet for cell g
W = Cells(Rows.Count, 19).End(xlUp).Row ' the next row in the worksheet for cell w
S15 = Cells(Rows.Count, 23).End(xlUp).Row ' the next row in the worksheet for s15
S70 = Cells(Rows.Count, 27).End(xlUp).Row ' the next row in the worksheet for s70
S17 = Cells(Rows.Count, 31).End(xlUp).Row ' the next row in the worksheet for s17
End Sub
'THIS IS WHERE I NEED HELP THE QUERY ABOVE OUTPUTS LIKE THIS
'Cell F 98
'Cell G 6
'S70 28
'Speed 20
'IS THERE A WAY OF FROM QUERY ABOVE TO PUT THE VALUES ie
'CELL F SHOULD GOTO F = Cells(Rows.Count, 11).End(xlUp).Row
'Cell G SHOULD GOTO G = Cells(Rows.Count, 15).End(xlUp).Row
'ETC...........BUT IF THERE IS NO OUTPUT ON A SPECIFIC CELL PUT A 0 IN SO NEXT TIME RUN all the rows match in line
'THEY WILL ALL BE ON SAME ROWS
also posted on mr excel http://www.mrexcel.com/board2/viewtopic.php?t=221276 (http://http//www.mrexcel.com/board2/viewtopic.php?t=221276)
thanks
Merc