Hello Guru's :D
I've been struggling with understanding XLS issued SQL commands to Access.
Ive been able to Patch/paste/modify some code to down load information about Staff from MdB to Excel. But having trouble modifiing the code so that I can delete a single record ! Hellp
THIS CODE WORKS TO DOWNLOAD TO XLS:
Sub GetRecordsForOneStaff()
Dim db As database
Dim Qd As QueryDef
Dim rs As Recordset
Dim qdParmQD As QueryDef
Dim SQL As String
Dim i As Integer
Dim vaTmp() As String
Dim dbLocation As String
' start with clean sheet
Sheets("Sheet1").Cells.ClearContents
' Set your database object.
Set db = _
Workspaces(0).OpenDatabase("C:\AccessTests\ISResources.mdb")
' 2. Create a SELECT statement.
SQL = "SELECT * " & _
" FROM tblAssignments" & _
" WHERE (tblAssignments.TaskOwner =[StaffWanted]) "
' 3. Create a named QueryDef object with your SQL statement.
On Error Resume Next ' only create the query once !!!
Set Qd = db.CreateQueryDef("Find Staff", SQL)
' 4. Set the QueryDef object parameters.
Set qdParmQD = db.querydefs("Find Staff")
qdParmQD("StaffWanted") = "Glen"
' 5. Execute the QueryDef.
Set rs = qdParmQD.OpenRecordset()
' 6. Issue a MoveLast followed by a MoveFirst
rs.MoveLast
rs.MoveFirst
' At this point, the database has been modified. The rest of this
' code displays the data on a worksheet. This is not necessary to
' complete the operation.
' 8. Collect field names.
For i = 0 To rs.Fields.Count - 1
Sheets("Sheet1").Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), _
Sheets("Sheet1").Cells(1, rs.Fields.Count)).Font.Bold = True
' 9. Issue a MoveFirst to move to the beginning of the recordset.
rs.MoveFirst
' 10. Use CopyFromRecordset to move the data onto the worksheet
Sheets("Sheet1").Range("A2").CopyFromRecordset rs
' 11. Select the sheet that data was written to and autofit the
' column widths.
Sheets("Sheet1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
' Select cell A1.
Range("A1").Select
' 12. Clean up and delete the QueryDef that was just created. This
' removes it from the database. Then close the objects.
'Qd.Close
rs.Close
db.Close
End Sub
BUT THIS CODE ERRORS OUT WHEN ATTEMPT RECORD DELETTION:
ERROR = data type conversion error
Sub DeleteARecord()
Dim db As database
Dim Qd As QueryDef
Dim rs As Recordset
Dim qdParmQD As QueryDef
Dim SQL As String
Dim i As Integer
Dim vaTmp() As String
Dim dbLocation As String
' Set your database object.
Set db = _
Workspaces(0).OpenDatabase("C:\AccessTests\ISResources.mdb")
' 2. Create a SELECT statement.
SQL = "DELETE * " & _
" FROM tblAssignments" & _
" WHERE (tblAssignments.ID = [IDwanted]) "
' 3. Create a named QueryDef object with your SQL statement.
'Set Qd = db.CreateQueryDef("FindID", SQL)
' 4. Set the QueryDef object parameters.
Set qdParmQD = db.querydefs("FindID")
qdParmQD("IDwanted") = "33"
' 5. Execute the QueryDef.
Set rs = qdParmQD.OpenRecordset("tblAssignments")
' 6. Issue a MoveLast followed by a MoveFirst
rs.MoveLast
rs.MoveFirst
rs.Close
db.Close
End Sub
Thx for any help you can give :D
I've been struggling with understanding XLS issued SQL commands to Access.
Ive been able to Patch/paste/modify some code to down load information about Staff from MdB to Excel. But having trouble modifiing the code so that I can delete a single record ! Hellp
THIS CODE WORKS TO DOWNLOAD TO XLS:
Sub GetRecordsForOneStaff()
Dim db As database
Dim Qd As QueryDef
Dim rs As Recordset
Dim qdParmQD As QueryDef
Dim SQL As String
Dim i As Integer
Dim vaTmp() As String
Dim dbLocation As String
' start with clean sheet
Sheets("Sheet1").Cells.ClearContents
' Set your database object.
Set db = _
Workspaces(0).OpenDatabase("C:\AccessTests\ISResources.mdb")
' 2. Create a SELECT statement.
SQL = "SELECT * " & _
" FROM tblAssignments" & _
" WHERE (tblAssignments.TaskOwner =[StaffWanted]) "
' 3. Create a named QueryDef object with your SQL statement.
On Error Resume Next ' only create the query once !!!
Set Qd = db.CreateQueryDef("Find Staff", SQL)
' 4. Set the QueryDef object parameters.
Set qdParmQD = db.querydefs("Find Staff")
qdParmQD("StaffWanted") = "Glen"
' 5. Execute the QueryDef.
Set rs = qdParmQD.OpenRecordset()
' 6. Issue a MoveLast followed by a MoveFirst
rs.MoveLast
rs.MoveFirst
' At this point, the database has been modified. The rest of this
' code displays the data on a worksheet. This is not necessary to
' complete the operation.
' 8. Collect field names.
For i = 0 To rs.Fields.Count - 1
Sheets("Sheet1").Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), _
Sheets("Sheet1").Cells(1, rs.Fields.Count)).Font.Bold = True
' 9. Issue a MoveFirst to move to the beginning of the recordset.
rs.MoveFirst
' 10. Use CopyFromRecordset to move the data onto the worksheet
Sheets("Sheet1").Range("A2").CopyFromRecordset rs
' 11. Select the sheet that data was written to and autofit the
' column widths.
Sheets("Sheet1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
' Select cell A1.
Range("A1").Select
' 12. Clean up and delete the QueryDef that was just created. This
' removes it from the database. Then close the objects.
'Qd.Close
rs.Close
db.Close
End Sub
BUT THIS CODE ERRORS OUT WHEN ATTEMPT RECORD DELETTION:
ERROR = data type conversion error
Sub DeleteARecord()
Dim db As database
Dim Qd As QueryDef
Dim rs As Recordset
Dim qdParmQD As QueryDef
Dim SQL As String
Dim i As Integer
Dim vaTmp() As String
Dim dbLocation As String
' Set your database object.
Set db = _
Workspaces(0).OpenDatabase("C:\AccessTests\ISResources.mdb")
' 2. Create a SELECT statement.
SQL = "DELETE * " & _
" FROM tblAssignments" & _
" WHERE (tblAssignments.ID = [IDwanted]) "
' 3. Create a named QueryDef object with your SQL statement.
'Set Qd = db.CreateQueryDef("FindID", SQL)
' 4. Set the QueryDef object parameters.
Set qdParmQD = db.querydefs("FindID")
qdParmQD("IDwanted") = "33"
' 5. Execute the QueryDef.
Set rs = qdParmQD.OpenRecordset("tblAssignments")
' 6. Issue a MoveLast followed by a MoveFirst
rs.MoveLast
rs.MoveFirst
rs.Close
db.Close
End Sub
Thx for any help you can give :D
