Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost - Best way to get data from Access 2003 to Excel 2003
From posts on 9/21-9/22 responding to Jacob Skaria's help Jacob: This is
what I have so far from your suggestion: Sub ExtractFromAccess() Set con = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") Dim strDB As String, strQ1 as String, lngRow As Long strDB = "\\phepsilon\groups\NOCC_Restricted\Administration \NOCC Employee File.mdb" 'this is typical path to the Employee File DB on our network strQ1 = "13-Week Points for CSR" 'The following is the SQL from strQ1 'SELECT DISTINCT Sum([Points]) AS SumofPoints 'FROM Absences 'WHERE (((Absences.Date)Date()-91) AND ((Absences.[Employee Number])= [Enter Employee Number])); con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}" rs.CursorLocation = adUseClient rs.Open "select * from <tablename", con, adOpenDynamic '--------------------------------- lngRow = 1 Do While rs.EOF = False Range("A" & lngRow) = rs("opid") Range("B" & lngRow) = rs("opname") lngRow = lngRow + 1 rs.MoveNext Loop '--------------------------------- rs.Close: con.Close Set rs = Nothing: Set con = Nothing End Sub ============================ At "rs.CursorLocation = adUseClient" I am getting error message "Arguments are of the wrong type, are out of the acceptable range, or are in conflict with one another." What is the issue here? As I had said, I am using a Query in the DB (called strQ1 above, with the actual SQL of the query just below it). Instead of referencing a specific Query do I have instead have to put the actual SQL from that Query in the next line where you have rs.Open "select * from <tablename", con, adOpenDynamic ? Obviously I will have to reference an employee number rather than prompting for it. Is this code putting the results from all employee numbers I reference into an Excel table at A1, B1, A2, B2, etc based on lngRow? I need to get results for up to 20 employee numbers. Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost - Best way to get data from Access 2003 to Excel 2003
I am responding to your previous thread..
"AZSteve" wrote: From posts on 9/21-9/22 responding to Jacob Skaria's help Jacob: This is what I have so far from your suggestion: Sub ExtractFromAccess() Set con = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") Dim strDB As String, strQ1 as String, lngRow As Long strDB = "\\phepsilon\groups\NOCC_Restricted\Administration \NOCC Employee File.mdb" 'this is typical path to the Employee File DB on our network strQ1 = "13-Week Points for CSR" 'The following is the SQL from strQ1 'SELECT DISTINCT Sum([Points]) AS SumofPoints 'FROM Absences 'WHERE (((Absences.Date)Date()-91) AND ((Absences.[Employee Number])= [Enter Employee Number])); con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}" rs.CursorLocation = adUseClient rs.Open "select * from <tablename", con, adOpenDynamic '--------------------------------- lngRow = 1 Do While rs.EOF = False Range("A" & lngRow) = rs("opid") Range("B" & lngRow) = rs("opname") lngRow = lngRow + 1 rs.MoveNext Loop '--------------------------------- rs.Close: con.Close Set rs = Nothing: Set con = Nothing End Sub ============================ At "rs.CursorLocation = adUseClient" I am getting error message "Arguments are of the wrong type, are out of the acceptable range, or are in conflict with one another." What is the issue here? As I had said, I am using a Query in the DB (called strQ1 above, with the actual SQL of the query just below it). Instead of referencing a specific Query do I have instead have to put the actual SQL from that Query in the next line where you have rs.Open "select * from <tablename", con, adOpenDynamic ? Obviously I will have to reference an employee number rather than prompting for it. Is this code putting the results from all employee numbers I reference into an Excel table at A1, B1, A2, B2, etc based on lngRow? I need to get results for up to 20 employee numbers. Thanks for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost - Best way to get data from Access 2003 to Excel 2003
adUseClient is a constant adn member of the Microsoft Active Data Objects
Library. If your project has not got a refrence to this, then use th evalue intsead, which is 3 adOpenDynamic is 2 rather than looping through each record, you can drop the entire recordset into the sheet WITH rs Range("A1").Resize( .RecordCount, .Fields.Count).CopyFromRecordset rs END WITH "AZSteve" wrote: From posts on 9/21-9/22 responding to Jacob Skaria's help Jacob: This is what I have so far from your suggestion: Sub ExtractFromAccess() Set con = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") Dim strDB As String, strQ1 as String, lngRow As Long strDB = "\\phepsilon\groups\NOCC_Restricted\Administration \NOCC Employee File.mdb" 'this is typical path to the Employee File DB on our network strQ1 = "13-Week Points for CSR" 'The following is the SQL from strQ1 'SELECT DISTINCT Sum([Points]) AS SumofPoints 'FROM Absences 'WHERE (((Absences.Date)Date()-91) AND ((Absences.[Employee Number])= [Enter Employee Number])); con.Open "DBQ=" & strDB & ";Driver={Microsoft Access Driver (*.mdb)}" rs.CursorLocation = adUseClient rs.Open "select * from <tablename", con, adOpenDynamic '--------------------------------- lngRow = 1 Do While rs.EOF = False Range("A" & lngRow) = rs("opid") Range("B" & lngRow) = rs("opname") lngRow = lngRow + 1 rs.MoveNext Loop '--------------------------------- rs.Close: con.Close Set rs = Nothing: Set con = Nothing End Sub ============================ At "rs.CursorLocation = adUseClient" I am getting error message "Arguments are of the wrong type, are out of the acceptable range, or are in conflict with one another." What is the issue here? As I had said, I am using a Query in the DB (called strQ1 above, with the actual SQL of the query just below it). Instead of referencing a specific Query do I have instead have to put the actual SQL from that Query in the next line where you have rs.Open "select * from <tablename", con, adOpenDynamic ? Obviously I will have to reference an employee number rather than prompting for it. Is this code putting the results from all employee numbers I reference into an Excel table at A1, B1, A2, B2, etc based on lngRow? I need to get results for up to 20 employee numbers. Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Best way to get data from Access 2003 to Excel 2003 | Excel Programming | |||
importing/linking data from an Access 2003 Query to an Excel 2003 | Excel Discussion (Misc queries) | |||
opening excel files from access 2003, win xp, office 2003 | Excel Discussion (Misc queries) | |||
Link Excel 2003 named range to Access 2003 Table | Excel Programming | |||
Transferring Large Amount of Data from Excel 2003 to Access 2003 | Excel Programming |