Larry Haydn
Board Regular
- Joined
- Jul 18, 2019
- Messages
- 207
- Office Version
- 365
- Platform
- Windows
I have an Excel application that reads and write to another Excel workbook that acts as a database.
The query is to select * when a record meets 2 criteria.
The selected record is then pasted onto a worksheet named [RECS]
From the enclosed image, you can see that the database (top image) record clearly contains data,
but the receiving worksheet (bottom image) does not have the data in fields W,X,Y.
What could be the problem?
The query is to select * when a record meets 2 criteria.
The selected record is then pasted onto a worksheet named [RECS]
From the enclosed image, you can see that the database (top image) record clearly contains data,
but the receiving worksheet (bottom image) does not have the data in fields W,X,Y.
What could be the problem?
VBA Code:
'=========================
' Connect to database
'=========================
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & dbpath & "\" & "DB.xlsx" & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
'===================
' Run the query
'===================
strSQL = "SELECT * FROM [FS$] WHERE LotNo='" & Batch & "' AND MoldNo='" & Mold & "';"
Set rs = cn.Execute(strSQL)
'========================
' Show the result(s)
'========================
RECS.Rows("2:1048576").ClearContents
RECS.Range("A2").CopyFromRecordset rs