Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need ADO Recordset Help
I posted a question yesterday, and was not able to get the help I needed. I
have since tried several things to get a connection to the AS400 and return a recordset using ADO. I have now been able to actually make the connection without getting an error, but when I attempt to get a recordset, I am still getting an error. I would really appreciate some help. Below is the code that I have now: Dim strConn As String Dim strSql As String Dim myconn As ADODB.Connection Dim rs As ADODB.Recordset strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" Set myconn = New ADODB.Connection myconn.Open strConn strSql = "SELECT * FROM BPCSF_GPM" Set rs = New ADODB.Recordset rs.Open (strSql), myconn 'here read some values from the recordset rs.Close myconn.Close Mr B |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need ADO Recordset Help
In this scenario, you do not need a connection Object, just a Recordset Object:
Set RS = CreateObject("ADODB.Recordset") strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" strSql = "SELECT * FROM BPCSF_GPM" RS.Open strSql,StrConn When you've finished with RS, RS.Close Set RS = Nothing. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need ADO Recordset Help
First, I want to thank you for your response. I do need help. I have been
able to accomplish this from Access with no problems, but for some reason I have a mental block on this one. I now have the following: Dim strConn As String Dim strSql As String Dim myconn As ADODB.Connection Dim rs As ADODB.Recordset strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" Set rs = CreateObject("ADODB.Recordset") strSql = "SELECT * FROM BPCSF_GPM" rs.Open strSql, strConn Dim varTest varTest = rs.Fields("PYEAR").Value rs.Close I have tested the connection string though the ODBC Admin and it reports the the connection is good and successful. When I step through the code and get to: rs.Open strSql, strConn I get the: "Run-time error '-2147217911 (80040e09)': Automation error Nothing I have tried has let me actually return a recordset. Is there anything wrong with the statement being assigned to the "strSql" variable? Mr B "AA2e72E" wrote: In this scenario, you do not need a connection Object, just a Recordset Object: Set RS = CreateObject("ADODB.Recordset") strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" strSql = "SELECT * FROM BPCSF_GPM" RS.Open strSql,StrConn When you've finished with RS, RS.Close Set RS = Nothing. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need ADO Recordset Help
Is BPCSF_GPM the table/query name? or is it BPCSF GPM?
You might try enclosing the table name in [] i.e. try the following: select * from [BPCSF_GPM] select * from [BPCSF GPM] and: select * from [BPCSF_GPM$] select * from [BPCSF GPM$] I looks like the table name is incorrect somehow. If you know Access, try: File | Get External Data | Import Tables From the dialogue, drop the Files of Type box and select ODBC DataSources(), specify your source and your table. (gives you an opportunity to see all the available tables/queries). "Mr B" wrote: First, I want to thank you for your response. I do need help. I have been able to accomplish this from Access with no problems, but for some reason I have a mental block on this one. I now have the following: Dim strConn As String Dim strSql As String Dim myconn As ADODB.Connection Dim rs As ADODB.Recordset strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" Set rs = CreateObject("ADODB.Recordset") strSql = "SELECT * FROM BPCSF_GPM" rs.Open strSql, strConn Dim varTest varTest = rs.Fields("PYEAR").Value rs.Close I have tested the connection string though the ODBC Admin and it reports the the connection is good and successful. When I step through the code and get to: rs.Open strSql, strConn I get the: "Run-time error '-2147217911 (80040e09)': Automation error Nothing I have tried has let me actually return a recordset. Is there anything wrong with the statement being assigned to the "strSql" variable? Mr B "AA2e72E" wrote: In this scenario, you do not need a connection Object, just a Recordset Object: Set RS = CreateObject("ADODB.Recordset") strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" strSql = "SELECT * FROM BPCSF_GPM" RS.Open strSql,StrConn When you've finished with RS, RS.Close Set RS = Nothing. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need ADO Recordset Help
Your connection string does not specify a Password: Isn't one needed?
"AA2e72E" wrote: Is BPCSF_GPM the table/query name? or is it BPCSF GPM? You might try enclosing the table name in [] i.e. try the following: select * from [BPCSF_GPM] select * from [BPCSF GPM] and: select * from [BPCSF_GPM$] select * from [BPCSF GPM$] I looks like the table name is incorrect somehow. If you know Access, try: File | Get External Data | Import Tables From the dialogue, drop the Files of Type box and select ODBC DataSources(), specify your source and your table. (gives you an opportunity to see all the available tables/queries). "Mr B" wrote: First, I want to thank you for your response. I do need help. I have been able to accomplish this from Access with no problems, but for some reason I have a mental block on this one. I now have the following: Dim strConn As String Dim strSql As String Dim myconn As ADODB.Connection Dim rs As ADODB.Recordset strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" Set rs = CreateObject("ADODB.Recordset") strSql = "SELECT * FROM BPCSF_GPM" rs.Open strSql, strConn Dim varTest varTest = rs.Fields("PYEAR").Value rs.Close I have tested the connection string though the ODBC Admin and it reports the the connection is good and successful. When I step through the code and get to: rs.Open strSql, strConn I get the: "Run-time error '-2147217911 (80040e09)': Automation error Nothing I have tried has let me actually return a recordset. Is there anything wrong with the statement being assigned to the "strSql" variable? Mr B "AA2e72E" wrote: In this scenario, you do not need a connection Object, just a Recordset Object: Set RS = CreateObject("ADODB.Recordset") strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" strSql = "SELECT * FROM BPCSF_GPM" RS.Open strSql,StrConn When you've finished with RS, RS.Close Set RS = Nothing. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need ADO Recordset Help
AA2e72E,
You are the man. The fact is that the actual name of the table is "BPCSF.GPM" with the dot not an underscore. Access does that because it cannont use the dot in the table name. The problem had nothing to do the the connection string, just the correct table name. Thank you very much. I was just not seeing the forest for the trees. Mr B "AA2e72E" wrote: Your connection string does not specify a Password: Isn't one needed? "AA2e72E" wrote: Is BPCSF_GPM the table/query name? or is it BPCSF GPM? You might try enclosing the table name in [] i.e. try the following: select * from [BPCSF_GPM] select * from [BPCSF GPM] and: select * from [BPCSF_GPM$] select * from [BPCSF GPM$] I looks like the table name is incorrect somehow. If you know Access, try: File | Get External Data | Import Tables From the dialogue, drop the Files of Type box and select ODBC DataSources(), specify your source and your table. (gives you an opportunity to see all the available tables/queries). "Mr B" wrote: First, I want to thank you for your response. I do need help. I have been able to accomplish this from Access with no problems, but for some reason I have a mental block on this one. I now have the following: Dim strConn As String Dim strSql As String Dim myconn As ADODB.Connection Dim rs As ADODB.Recordset strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" Set rs = CreateObject("ADODB.Recordset") strSql = "SELECT * FROM BPCSF_GPM" rs.Open strSql, strConn Dim varTest varTest = rs.Fields("PYEAR").Value rs.Close I have tested the connection string though the ODBC Admin and it reports the the connection is good and successful. When I step through the code and get to: rs.Open strSql, strConn I get the: "Run-time error '-2147217911 (80040e09)': Automation error Nothing I have tried has let me actually return a recordset. Is there anything wrong with the statement being assigned to the "strSql" variable? Mr B "AA2e72E" wrote: In this scenario, you do not need a connection Object, just a Recordset Object: Set RS = CreateObject("ADODB.Recordset") strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" strSql = "SELECT * FROM BPCSF_GPM" RS.Open strSql,StrConn When you've finished with RS, RS.Close Set RS = Nothing. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need ADO Recordset Help
Could you take a look at the follow sql statment? When I use the simple sql
statment that I posted, the recordset is returned, but when I attempt to use the following statement, it fails: strSql = "SELECT Max(PYEAR) AS MaxOfPYEAR FROM [BPCSF.GPM] " _ & "WHERE (((POPNCL)=""Y"") AND ((PAROFF)<21))" This statement works from Access. I have made the corrections to the table name. Mr B "AA2e72E" wrote: Your connection string does not specify a Password: Isn't one needed? "AA2e72E" wrote: Is BPCSF_GPM the table/query name? or is it BPCSF GPM? You might try enclosing the table name in [] i.e. try the following: select * from [BPCSF_GPM] select * from [BPCSF GPM] and: select * from [BPCSF_GPM$] select * from [BPCSF GPM$] I looks like the table name is incorrect somehow. If you know Access, try: File | Get External Data | Import Tables From the dialogue, drop the Files of Type box and select ODBC DataSources(), specify your source and your table. (gives you an opportunity to see all the available tables/queries). "Mr B" wrote: First, I want to thank you for your response. I do need help. I have been able to accomplish this from Access with no problems, but for some reason I have a mental block on this one. I now have the following: Dim strConn As String Dim strSql As String Dim myconn As ADODB.Connection Dim rs As ADODB.Recordset strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" Set rs = CreateObject("ADODB.Recordset") strSql = "SELECT * FROM BPCSF_GPM" rs.Open strSql, strConn Dim varTest varTest = rs.Fields("PYEAR").Value rs.Close I have tested the connection string though the ODBC Admin and it reports the the connection is good and successful. When I step through the code and get to: rs.Open strSql, strConn I get the: "Run-time error '-2147217911 (80040e09)': Automation error Nothing I have tried has let me actually return a recordset. Is there anything wrong with the statement being assigned to the "strSql" variable? Mr B "AA2e72E" wrote: In this scenario, you do not need a connection Object, just a Recordset Object: Set RS = CreateObject("ADODB.Recordset") strConn = "Provider=MSDASQL.1; " _ & "Persist Security Info=False; " _ & "User ID=ACCTING; " _ & "Data Source=ACCOUNTING; " _ & "Initial Catalog=S408" strSql = "SELECT * FROM BPCSF_GPM" RS.Open strSql,StrConn When you've finished with RS, RS.Close Set RS = Nothing. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ADO recordset error | Excel Programming | |||
Type recordset/recordset? | Excel Programming | |||
Recordset | Excel Programming | |||
Looping Through A Recordset | Excel Programming | |||
Looping Through A Recordset | Excel Programming |