I'm having trouble with the limitation of Excel 16 bit, basically I am
downloading values from a database that are greater than 16 bits, is there
anyway I could bypass this limitation? My code can be seen below, any help
will be appreciated,

Sub SPICEdownload_query()
On Error GoTo datapullerr
Dim wk As Workbook
Dim data As Worksheet, para As Worksheet
Dim Sql$
Set wk = ThisWorkbook
Set data = wk.Sheets("Divisors from SQL")

Dim wrkodbc As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim r As Range
Dim ts As String
Dim MyDouble As Double

Set wrkodbc = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set db = wrkodbc.OpenDatabase("Spice", , ,
"ODBC;DSN=DSNNAME;UID=UID;pwd=PWD;SERVER=SERVE R;")
ts = ""
i = 3
Sql = data.Cells(2, 4)
Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)



If rs.EOF Then
MsgBox "index value not available"
Else
Dim qt As QueryTable
data.Range("a3:bb60000").ClearContents ' empty sheet
Set qt = data.QueryTables.Add(rs, data.Range("a3"))
qt.AdjustColumnWidth = False ' qt need not adjust width
qt.BackgroundQuery = False ' dont run on background.
qt.PreserveFormatting = True
qt.RefreshStyle = xlOverwriteCells
qt.FieldNames = True
qt.Refresh
tmpstr = data.Name & "!" & qt.Name
qt.Delete ' rid of the querytable
' delete the name that is created is using query table
For Each n In wk.Names
If n.Name = tmpstr Then
n.Delete
Exit For
End If
Next n
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Exit Sub
datapullerr:
MsgBox (" Download Error; Operation aborted ")
'rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

I've found that the problem occurs before it hits the IF statement, I used a
debug.print statement to see this, any help will be appreciated,


Thank you

Take a look at the SQL string that is being passed. The string will be
limited to 256 characters. You can however pass a concatenated string...

dim SQL1 as string
dim SQL2 as string

SQL1 = left(cells(2,4), 255)
SQL2 = mid(cells(2,4), 256, 255)

Set rs = db.OpenRecordset(SQL1 & SQL2, dbOpenSnapshot)


--
HTH...

Jim Thomlinson


"drinese18" wrote:

I'm having trouble with the limitation of Excel 16 bit, basically I am
downloading values from a database that are greater than 16 bits, is there
anyway I could bypass this limitation? My code can be seen below, any help
will be appreciated,

Sub SPICEdownload_query()
On Error GoTo datapullerr
Dim wk As Workbook
Dim data As Worksheet, para As Worksheet
Dim Sql$
Set wk = ThisWorkbook
Set data = wk.Sheets("Divisors from SQL")

Dim wrkodbc As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim r As Range
Dim ts As String
Dim MyDouble As Double

Set wrkodbc = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set db = wrkodbc.OpenDatabase("Spice", , ,
"ODBC;DSN=DSNNAME;UID=UID;pwd=PWD;SERVER=SERVE R;")
ts = ""
i = 3
Sql = data.Cells(2, 4)
Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)



If rs.EOF Then
MsgBox "index value not available"
Else
Dim qt As QueryTable
data.Range("a3:bb60000").ClearContents ' empty sheet
Set qt = data.QueryTables.Add(rs, data.Range("a3"))
qt.AdjustColumnWidth = False ' qt need not adjust width
qt.BackgroundQuery = False ' dont run on background.
qt.PreserveFormatting = True
qt.RefreshStyle = xlOverwriteCells
qt.FieldNames = True
qt.Refresh
tmpstr = data.Name & "!" & qt.Name
qt.Delete ' rid of the querytable
' delete the name that is created is using query table
For Each n In wk.Names
If n.Name = tmpstr Then
n.Delete
Exit For
End If
Next n
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Exit Sub
datapullerr:
MsgBox (" Download Error; Operation aborted ")
'rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

I've found that the problem occurs before it hits the IF statement, I used a
debug.print statement to see this, any help will be appreciated,


Thank you

I incorapted the string of code into my code but for some reason it is coming
up with an error when it gets to opening the recordset, you can see how I've
incorporated it below:

Sub SPICEdownload_query()
On Error GoTo datapullerr
Dim wk As Workbook
Dim data As Worksheet, para As Worksheet
Dim Sql$
Set wk = ThisWorkbook
Set data = wk.Sheets("Divisors from SQL")

Dim wrkodbc As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim r As Range
Dim ts As String


Dim SQL1 As String
Dim SQL2 As String





Set wrkodbc = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set db = wrkodbc.OpenDatabase("Spice", , ,
"ODBC;DSN=SPICE;UID=eqiqry;pwd=eqiqry;SERVER=PSDR1 .MHF2.MHF.MHC;")
ts = ""
i = 3
'Sql = data.Cells(2, 4)

SQL1 = Left(Cells(2, 4), 255)
SQL2 = Mid(Cells(2, 4), 256, 255)

Set rs = db.OpenRecordset(SQL1 & SQL2, dbOpenSnapshot)

'Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)





If rs.EOF Then
MsgBox "index value not available"
Else
Dim qt As QueryTable
data.Range("a3:bb60000").ClearContents ' empty sheet
Set qt = data.QueryTables.Add(rs, data.Range("a3"))
qt.AdjustColumnWidth = False ' qt need not adjust width
qt.BackgroundQuery = False ' dont run on background.
qt.PreserveFormatting = True
qt.RefreshStyle = xlOverwriteCells
qt.FieldNames = True
qt.Refresh
tmpstr = data.Name & "!" & qt.Name
qt.Delete ' rid of the querytable
' delete the name that is created is using query table
For Each n In wk.Names
If n.Name = tmpstr Then
n.Delete
Exit For
End If
Next n
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Exit Sub
datapullerr:
MsgBox (" Download Error; Operation aborted ")
'rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub


For some reason it's just running to the error msg so not sure as to what is
going because it does open to the database, I checked it with a debug print
but by the time it gets to opening the recordset it just fails, what you
think might be the problem?


"Jim Thomlinson" wrote:

Take a look at the SQL string that is being passed. The string will be
limited to 256 characters. You can however pass a concatenated string...

dim SQL1 as string
dim SQL2 as string

SQL1 = left(cells(2,4), 255)
SQL2 = mid(cells(2,4), 256, 255)

Set rs = db.OpenRecordset(SQL1 & SQL2, dbOpenSnapshot)


--
HTH...

Jim Thomlinson


"drinese18" wrote:

I'm having trouble with the limitation of Excel 16 bit, basically I am
downloading values from a database that are greater than 16 bits, is there
anyway I could bypass this limitation? My code can be seen below, any help
will be appreciated,

Sub SPICEdownload_query()
On Error GoTo datapullerr
Dim wk As Workbook
Dim data As Worksheet, para As Worksheet
Dim Sql$
Set wk = ThisWorkbook
Set data = wk.Sheets("Divisors from SQL")

Dim wrkodbc As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim r As Range
Dim ts As String
Dim MyDouble As Double

Set wrkodbc = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set db = wrkodbc.OpenDatabase("Spice", , ,
"ODBC;DSN=DSNNAME;UID=UID;pwd=PWD;SERVER=SERVE R;")
ts = ""
i = 3
Sql = data.Cells(2, 4)
Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)



If rs.EOF Then
MsgBox "index value not available"
Else
Dim qt As QueryTable
data.Range("a3:bb60000").ClearContents ' empty sheet
Set qt = data.QueryTables.Add(rs, data.Range("a3"))
qt.AdjustColumnWidth = False ' qt need not adjust width
qt.BackgroundQuery = False ' dont run on background.
qt.PreserveFormatting = True
qt.RefreshStyle = xlOverwriteCells
qt.FieldNames = True
qt.Refresh
tmpstr = data.Name & "!" & qt.Name
qt.Delete ' rid of the querytable
' delete the name that is created is using query table
For Each n In wk.Names
If n.Name = tmpstr Then
n.Delete
Exit For
End If
Next n
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Exit Sub
datapullerr:
MsgBox (" Download Error; Operation aborted ")
'rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

I've found that the problem occurs before it hits the IF statement, I used a
debug.print statement to see this, any help will be appreciated,


Thank you