Anyone,

I have a user form in Excel where the user can type a description in a
textbox. I wrote SQL INSERT query in VBA to write the description to a
closed workbook that acts as a database file. Im using the ADO reference
2.7. When the text exceeds 255 characters I receive an error. I know an
Excel cell can hold more than 32k characters, how can I get the text from the
SQL statement to write to the cell in the closed workbook? Another issue Im
having is the SQL errors when a single quote is typed, is there a simple fix
for this?

I look forward to some help.

In response to the 255 limit you just need to break up your select statement
and pass it in as concatenated arguments. Here is some really generic code I
use...

Private Const m_cDBLocation As String = "C:\MyDatabase.mdb"

Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean)
As ADODB.Recordset
Dim strConnection As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
End Function

Sub Example()
Dim rst As ADODB.Recordset

Set rst = RunQuery("Select *", "From tblRegions", "", ";", False)

End Sub

--
HTH...

Jim Thomlinson


"DB" wrote:

Anyone,

I have a user form in Excel where the user can type a description in a
textbox. I wrote SQL INSERT query in VBA to write the description to a
closed workbook that acts as a database file. Im using the ADO reference
2.7. When the text exceeds 255 characters I receive an error. I know an
Excel cell can hold more than 32k characters, how can I get the text from the
SQL statement to write to the cell in the closed workbook? Another issue Im
having is the SQL errors when a single quote is typed, is there a simple fix
for this?

I look forward to some help.

Jim,
This is not the issue. The length of the text variable for the textbox
(me.txbDesc.text) is over 255 characters and causes the error. Are there ADO
methods to make this work?

"Jim Thomlinson" wrote:

In response to the 255 limit you just need to break up your select statement
and pass it in as concatenated arguments. Here is some really generic code I
use...

Private Const m_cDBLocation As String = "C:\MyDatabase.mdb"

Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean)
As ADODB.Recordset
Dim strConnection As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
End Function

Sub Example()
Dim rst As ADODB.Recordset

Set rst = RunQuery("Select *", "From tblRegions", "", ";", False)

End Sub

--
HTH...

Jim Thomlinson


"DB" wrote:

Anyone,

I have a user form in Excel where the user can type a description in a
textbox. I wrote SQL INSERT query in VBA to write the description to a
closed workbook that acts as a database file. Im using the ADO reference
2.7. When the text exceeds 255 characters I receive an error. I know an
Excel cell can hold more than 32k characters, how can I get the text from the
SQL statement to write to the cell in the closed workbook? Another issue Im
having is the SQL errors when a single quote is typed, is there a simple fix
for this?

I look forward to some help.