B
Bobbo
I am using the code below to get some data out of access and put it in column
"G". Instead of puting it in "G" how can I append it to what is in Column "A"
' look in names database and get ORG code based on user
'Needs reference the Axtive X Library 2.0 or higher
Const projectIDColumn = "A" 'column B that have project number in it
Const projectDescColumn = "G" 'column C that I would like to populate
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim looper As Long
Dim cellPointer As Variant
'C:\PathToYourMdb\Database.mdb (Change)
'
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & ThisWorkbook.Path & "\names.mdb;Persist Security
Info=False"
Set cnn = New ADODB.Connection
cnn.Open strConn
For looper = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
Set cellPointer = Worksheets("Sheet1").Range(projectIDColumn & looper)
'If you project number field is text use this sSQL
sSQL = "SELECT EMP.* FROM EMP WHERE (((EMP.FullName)='" & cellPointer &
"'));"
'If you project number field is number use this sSQL
'sSQL = "SELECT tblProjects.* FROM tblProjects WHERE
(((tblProjects.PROJECT_NUM)=" & cellPointer & "));"
Set rs = New ADODB.Recordset
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
If Not IsNull(rs.Fields("OrgCode").Value) Then
Range(projectDescColumn & looper) = rs.Fields("OrgCode").Value
End If
rs.Close
Set rs = Nothing
Next looper
cnn.Close
Set cnn = Nothing
"G". Instead of puting it in "G" how can I append it to what is in Column "A"
' look in names database and get ORG code based on user
'Needs reference the Axtive X Library 2.0 or higher
Const projectIDColumn = "A" 'column B that have project number in it
Const projectDescColumn = "G" 'column C that I would like to populate
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim looper As Long
Dim cellPointer As Variant
'C:\PathToYourMdb\Database.mdb (Change)
'
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & ThisWorkbook.Path & "\names.mdb;Persist Security
Info=False"
Set cnn = New ADODB.Connection
cnn.Open strConn
For looper = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
Set cellPointer = Worksheets("Sheet1").Range(projectIDColumn & looper)
'If you project number field is text use this sSQL
sSQL = "SELECT EMP.* FROM EMP WHERE (((EMP.FullName)='" & cellPointer &
"'));"
'If you project number field is number use this sSQL
'sSQL = "SELECT tblProjects.* FROM tblProjects WHERE
(((tblProjects.PROJECT_NUM)=" & cellPointer & "));"
Set rs = New ADODB.Recordset
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
If Not IsNull(rs.Fields("OrgCode").Value) Then
Range(projectDescColumn & looper) = rs.Fields("OrgCode").Value
End If
rs.Close
Set rs = Nothing
Next looper
cnn.Close
Set cnn = Nothing