ODBC data to variable

[Marcster]

Hi all,

I have some code:
[VBA]
Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=False, _
Connection:= _

"CONNECTION_STRING_HERE" _

, SQLStatement:= _

"SQL_STATEMENT_HERE" _

, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _

WritePasswordTemplate:="", DataSource:="", From:=-1, To:=-1, _

IncludeFields:=True
[/VBA]
which will insert some data from a database (not Access) via ODBC
into the active document where the cursor is.
I want to be able to have more control over where the data gets inserted.
What I want to be able to do is set the above to a variable then using VBA and bookmarks
I can insert the data via the variable to where I it to go.
Can this be done?.

Thanks,

Marcster.

[Marcster]

When I recorded a macro (code displayed above), it inserted the
data in the document.
But when I tried running the code later, an error message is displayed:

Run-time error '9105':
String is longer than 255 characters

Is there a work around?.

Thanks,

Marcster.

[Tommy]

Hi Marcster,
You are only inserting 1 record also. I have some ODBC stuff but it is set-up for PostgreSQL, I may or may not have some Access I usually use adoc, & jet for that. It is all in VB but can at least, if not, shed more light on the subject.
On the doc I prefer FormFields, I like to see where it is supposed to go and work with that, I know that bookmarks are another (or vice versa) type of formfield. I just learned to use the formfileds first .

I'll be back in a few I need to get the source cleaned up a little

[Marcster]

Hi Tommy,
Thanks for replying.
How do I set up the doc using FormFields?.
I go to Insert > Field...
Categories: Mail Merge
Field names: Database
Options
\c [Connection String]
\s [SQL String]
but the text box is too short to input the [Connection String] & [SQL String] in.
I'm using Word 2000.

Marcster.

[Tommy]

Under view I pick toolbars and select Forms. Then I pick the one that looks like ab| this will insert a formfield at the curser location. I also have the Form Field Shading on so I can see the thing .

The way I was going to show how to do the ODBC was by hand, you are trying to automate it, I had better let someone else help out, because I am not familiar with the way you are trying to do this.

Here is some code if it will help:
[VBA]
Option Explicit
Public IDnum As Long
Public Table As String
Public cn As New rdoConnection
Public SQL As String
Public Database As String
Public Conn As Boolean
Public Function ExecSql()
On Error GoTo CnEh
If Not Conn Then
If INHouse Then
cn.CursorDriver = rdUseOdbc
cn.Connect = "DSN=;DATABASE=" & Database & ";" & _
"SERVER=;PORT=;UID=;PWD=;READONLY=0;PROTOCOL=6.4;" & _
"FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;" & _
"SHOWSYSTEMTABLES=1;CONNSETTINGS="
End If
cn.EstablishConnection
Conn = True
End If
cn.Execute SQL, rdExecDirect
cn.BeginTrans
cn.CommitTrans
On Error GoTo 0
Exit Function
CnEh:
Err.Clear
On Error GoTo 0
End Function

'*************** sampleusage ****************
Dim Total As Long
Dim RowBuf As Variant
Dim Entry As String
Dim DisRS As rdoResultset
SQL = "select " & Table & ".name from " & Table & " where " _
& Table & ".name LIKE '" & sel & "%' order by name"
ExecSql
Set DisRS = cn.OpenResultset(SQL, rdOpenForwardOnly)
RowBuf = DisRS.GetRows(50)
Total = UBound(RowBuf, 2) + 1
'*** I am filling a combo box here with the data from the record set *********
For i = 0 To Total - 1
Entry = RowBuf(0, i)
Abox.AddItem Entry
Next i


[/VBA]

[Marcster]

Thanks Tommy,
I'll look into the code you've provided and let you know how I get on.

Does anyone know of a work around for the following error:

Run-time error '9105':
String is longer than 255 characters

Thanks,

Marcster.

[Tommy]

Where is the error occuring? There are a couple of workarounds.

[Marcster]

The error appears when the macro gets run.
It gets displayed in message box with 'Microsoft Visual Basic' as the title and
two buttons, OK and Help. Pressing Help only opens the Help without showing anything!.

Word doesn't show where the error is, unlike a usual error where the error line is highlighted in the VBE.

I think it's because the SQLStatement is too long.

As a work around I'm going to set up a procedure for each piece of data I want from the database then can insert the data into the document.

I'll let you know how I get on,

If you know a different way, please let me know.

Thanks,

Marcster.

[Tommy]

Can you post the doc and the db? The code I posted earlier is for remote access of a database. Which it looks like from what you are saying is the way we are going to have to go. At least that's what I think now FWIW

[TonyJollans]

Don't think I can help much with the whole thing, but I have run into the 255 character limit for SQL statements before and I think it's absolute. I think it may be a constraint of the connection, nothing directly to do with Word.