No. Whatever data you pass from Access to Excel will remain the same.
The problem is that the automation code is reading the data from an ODBC
table and the data is being truncated at the ODBC table.
One thing you could do to simplify this data transfer is to write the
same ADO code I posted in Excel in Excel's visual Basic Editor. Make
the same reference to
Microsoft ActiveX Data Objects 2.X Library
and add the code to a code module. This will be a Macro in Excel.
cmd.CommandText = "Select * from tblx"
the ADO command object will use the same connection string to your sql
server as the connection string in Access.
So Access is basically the middle man for this data transfer. You are
really transferring data from the Sql Server to Excel. It is much
simpler to bypass the middle man (Access). Even if your situation would
not be conducive to using Excel programming I would give that a try so
you can see how it works. Here is some sample code that you would use
in Excel:
Sub GetDataFromSqlS erver()
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
Dim rng As Range, i As Integer
Dim strSql As String, WkBk As Workbook, sht As Worksheet
strSql = "SELECT * FROM yourTbl WHERE something = 'something'"
Set WkBk = ActiveWorkbook
Set sht = WkBk.ActiveShee t
Set rng = sht.Range("A1:B H1")
cmd.ActiveConne ction = "Provider=SQLOL EDB; Data
Source=yourServ er;Database=you rDB;Trusted_Con nection=Yes"
cmd.ActiveConne ction.CursorLoc ation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = strSql
DoEvents
Set RS = cmd.Execute
sht.Range("A2") .CopyFromRecord set RS
For i = 0 To RS.Fields.Count - 1
rng(1, i + 1) = RS(i).Name
Next
rng.Font.Bold = True
rng.Font.ColorI ndex = 5
End Sub
The connection string here is based on Windows authentication. If this
connection string doesn't work then try using your UserID and Password
as follows
cmd.ActiveConne ction = "Provider=SQLOL EDB; Data
Source=yourServ er;Database=you rDB;uid=steve;p wd=test"
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
