Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with DAO and SQL Server
I am trying to return a DAO recordset from SQL Server so that I can use
CopyFromRecordSet to populate some data in Excel 97. The below creates the recordset but the CopyFromRecordSet fails with : error -2147467259 (80004005) Operation is not supported for this type of object If I change to another database (SpecData) and change the strSQL to select a table without the owner prefix the code works. The permissions on the two databases are identical. Can anyone point me in the right direction? Function OpenConnection() As Boolean Dim wrk As DAO.Workspace Dim cnn As DAO.Connection Dim rst As DAO.Recordset Dim strConnect, strSQL As String On Error GoTo Err_OpenConnection ' Create connnect string. strConnect = "ODBC;DSN=JDEEXTRACT;UID=ADG;PWD=;DATABASE=JDEEXTR ACT" ' Create SQL string. strSQL = "SELECT * FROM dbo.XF4111;" ' Create ODBCDirect workspace. Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "ADG", "", dbUseODBC) ' Open connection. Set cnn = wrk.OpenConnection("MyCon", dbDriverNoPrompt, True, strConnect) ' Open recordset on connection. Set rst = cnn.OpenRecordset(strSQL, dbOpenDynaset) On Error GoTo 0 Worksheets("Sheet1").Range("A2").CopyFromRecordset rst Debug.Print rst.RecordCount OpenConnection = True Exit_OpenConnection: rst.Close cnn.Close wrk.Close Set rst = Nothing Set cnn = Nothing Set wrk = Nothing Exit Function Err_OpenConnection: MsgBox "Error " & Err & ": " & Err.Description OpenConnection = False Resume Exit_OpenConnection End Function Also I did find a kb article which shows how to copy the recordset to an array, then transpose it to copy into a range of a equal size (code works for all versions of office from 97 onwards). This also failed. I tried to set one cell to the value of one element in the array, this failed. The array in the example was stored in a variable declared as variant. If I changed the code from Worksheets("Sheet1").cells(2,1).value=v(1,1) to Worksheets("Sheet1").cells(2,1).value=trim$(v(1,1) ) the code worked. The Data in the element was packed with spaces eg "ABC ". By looking at the field type of each field I am able to assign the cell values using either the val() or trim$() functions, without these the assignment fails. Is there a problem with assigning data of type variant to cells / ranges? -- Tony Green |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with DAO and SQL Server
Possibly messy, but it would surely work, move to the first record then
write one record at a time to the database, moving to the next record until EOF is reached? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with DAO and SQL Server
Hi Aidan
Yes, I can work through the Recordset and post each field, the average size of recordset returned by my query will be 20,000 records by 21 fields. This is slow, the copyfromrecordset and array copy methods are significantly faster. I was hoping that someone else might of found this problem and a simple solution. Regards -- Tony Green " wrote: Possibly messy, but it would surely work, move to the first record then write one record at a time to the database, moving to the next record until EOF is reached? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with DAO and SQL Server
Just a thought, but the knowledge base article said an array of equal
size - given that you know the number of fields (columns) and the number of records (rows) why not use these values to get the actual size of the array and set the entire value at this point? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with DAO and SQL Server
Tried this, the code to resize the range was included. I think the problem is
that Excel fails to interpret the data types correctly. -- Tony Green " wrote: Just a thought, but the knowledge base article said an array of equal size - given that you know the number of fields (columns) and the number of records (rows) why not use these values to get the actual size of the array and set the entire value at this point? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with DAO and SQL Server
What are the column datatypes in the table you're querying?
Is there one particular column which causes the problem? If so (maybe the space-padded one?) you could try casting the type in the SQL or using TRIM() in your query (if available, not familiar with SQL Server) -- Tim Williams Palo Alto, CA "ADG" wrote in message ... I am trying to return a DAO recordset from SQL Server so that I can use CopyFromRecordSet to populate some data in Excel 97. The below creates the recordset but the CopyFromRecordSet fails with : error -2147467259 (80004005) Operation is not supported for this type of object If I change to another database (SpecData) and change the strSQL to select a table without the owner prefix the code works. The permissions on the two databases are identical. Can anyone point me in the right direction? Function OpenConnection() As Boolean Dim wrk As DAO.Workspace Dim cnn As DAO.Connection Dim rst As DAO.Recordset Dim strConnect, strSQL As String On Error GoTo Err_OpenConnection ' Create connnect string. strConnect = "ODBC;DSN=JDEEXTRACT;UID=ADG;PWD=;DATABASE=JDEEXTR ACT" ' Create SQL string. strSQL = "SELECT * FROM dbo.XF4111;" ' Create ODBCDirect workspace. Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "ADG", "", dbUseODBC) ' Open connection. Set cnn = wrk.OpenConnection("MyCon", dbDriverNoPrompt, True, strConnect) ' Open recordset on connection. Set rst = cnn.OpenRecordset(strSQL, dbOpenDynaset) On Error GoTo 0 Worksheets("Sheet1").Range("A2").CopyFromRecordset rst Debug.Print rst.RecordCount OpenConnection = True Exit_OpenConnection: rst.Close cnn.Close wrk.Close Set rst = Nothing Set cnn = Nothing Set wrk = Nothing Exit Function Err_OpenConnection: MsgBox "Error " & Err & ": " & Err.Description OpenConnection = False Resume Exit_OpenConnection End Function Also I did find a kb article which shows how to copy the recordset to an array, then transpose it to copy into a range of a equal size (code works for all versions of office from 97 onwards). This also failed. I tried to set one cell to the value of one element in the array, this failed. The array in the example was stored in a variable declared as variant. If I changed the code from Worksheets("Sheet1").cells(2,1).value=v(1,1) to Worksheets("Sheet1").cells(2,1).value=trim$(v(1,1) ) the code worked. The Data in the element was packed with spaces eg "ABC ". By looking at the field type of each field I am able to assign the cell values using either the val() or trim$() functions, without these the assignment fails. Is there a problem with assigning data of type variant to cells / ranges? -- Tony Green |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with DAO and SQL Server
Types as returned by field type are 18, 20 and 23. The data is either a
String, Long or Date/Time. Will look at what is possible in the SQL string sent to MS SQL SERVER. I am not familar with what can be done on this platform. Regards -- Tony Green "Tim Williams" wrote: What are the column datatypes in the table you're querying? Is there one particular column which causes the problem? If so (maybe the space-padded one?) you could try casting the type in the SQL or using TRIM() in your query (if available, not familiar with SQL Server) -- Tim Williams Palo Alto, CA "ADG" wrote in message ... I am trying to return a DAO recordset from SQL Server so that I can use CopyFromRecordSet to populate some data in Excel 97. The below creates the recordset but the CopyFromRecordSet fails with : error -2147467259 (80004005) Operation is not supported for this type of object If I change to another database (SpecData) and change the strSQL to select a table without the owner prefix the code works. The permissions on the two databases are identical. Can anyone point me in the right direction? Function OpenConnection() As Boolean Dim wrk As DAO.Workspace Dim cnn As DAO.Connection Dim rst As DAO.Recordset Dim strConnect, strSQL As String On Error GoTo Err_OpenConnection ' Create connnect string. strConnect = "ODBC;DSN=JDEEXTRACT;UID=ADG;PWD=;DATABASE=JDEEXTR ACT" ' Create SQL string. strSQL = "SELECT * FROM dbo.XF4111;" ' Create ODBCDirect workspace. Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "ADG", "", dbUseODBC) ' Open connection. Set cnn = wrk.OpenConnection("MyCon", dbDriverNoPrompt, True, strConnect) ' Open recordset on connection. Set rst = cnn.OpenRecordset(strSQL, dbOpenDynaset) On Error GoTo 0 Worksheets("Sheet1").Range("A2").CopyFromRecordset rst Debug.Print rst.RecordCount OpenConnection = True Exit_OpenConnection: rst.Close cnn.Close wrk.Close Set rst = Nothing Set cnn = Nothing Set wrk = Nothing Exit Function Err_OpenConnection: MsgBox "Error " & Err & ": " & Err.Description OpenConnection = False Resume Exit_OpenConnection End Function Also I did find a kb article which shows how to copy the recordset to an array, then transpose it to copy into a range of a equal size (code works for all versions of office from 97 onwards). This also failed. I tried to set one cell to the value of one element in the array, this failed. The array in the example was stored in a variable declared as variant. If I changed the code from Worksheets("Sheet1").cells(2,1).value=v(1,1) to Worksheets("Sheet1").cells(2,1).value=trim$(v(1,1) ) the code worked. The Data in the element was packed with spaces eg "ABC ". By looking at the field type of each field I am able to assign the cell values using either the val() or trim$() functions, without these the assignment fails. Is there a problem with assigning data of type variant to cells / ranges? -- Tony Green |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
aauugghhh...#div/o problems & various average formula problems | Excel Worksheet Functions | |||
SQL Server -- Bulk Insert from Excel to SQL Server | Excel Discussion (Misc queries) | |||
Excel 2003 Error Cannot Locate the Internet Server or Proxy Server | Excel Discussion (Misc queries) | |||
problem updating link from Novell server to windows server #REF er | Excel Discussion (Misc queries) | |||
excel focus problems with custom RTD server (real time data) | Excel Programming |