Hello everybody.
I'm am using VBA now for a little while and I'm trying to automate some tasks in word using data from a access database.
I have the following problem:
I use VBA to open a ODBC connection to the database. Later on I will use this connection to get the data I need for a mailmerge.
Problem is that my SQL statement is more then 1000 characters long. (this is due to long fieldnames, lots of fields and tables and long expressions) the method only supports two SQL parts of 255 characters. How will I get my data in word?
Any help I would appreciate a lot!
this is my current code:
*****************************
Dim oApp As Object
Set oApp = CreateObject("Word.Application"
oApp.Visible = True
'this opens the main document
oApp.Documents.Open "C:\windows\desktop\letter.doc"
Dim Constr As String
Dim strSQL2 As String
Dim strSQL1 As String
'setting up the connection properties
Constr = "DSN=MS Access Database;DBQ=c:\WINDOWS\Desktop\AIS-010731-1-K-BE.mdb;DefaultDir=c:\WINDOWS\Desktop;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
'setting up the SQL
strSQL = blabla bla bla bla. (very long)
'opening the datasource and importing the data
oApp.ActiveDocument.MailMerge.OpenDataSource "C:\windows\desktop\AIS-010731-1-K-BE.mdb", Connection:=Constr, SQLStatement:=strSQL
'end of code
****************************
thanks
I'm am using VBA now for a little while and I'm trying to automate some tasks in word using data from a access database.
I have the following problem:
I use VBA to open a ODBC connection to the database. Later on I will use this connection to get the data I need for a mailmerge.
Problem is that my SQL statement is more then 1000 characters long. (this is due to long fieldnames, lots of fields and tables and long expressions) the method only supports two SQL parts of 255 characters. How will I get my data in word?
Any help I would appreciate a lot!
this is my current code:
*****************************
Dim oApp As Object
Set oApp = CreateObject("Word.Application"
oApp.Visible = True
'this opens the main document
oApp.Documents.Open "C:\windows\desktop\letter.doc"
Dim Constr As String
Dim strSQL2 As String
Dim strSQL1 As String
'setting up the connection properties
Constr = "DSN=MS Access Database;DBQ=c:\WINDOWS\Desktop\AIS-010731-1-K-BE.mdb;DefaultDir=c:\WINDOWS\Desktop;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
'setting up the SQL
strSQL = blabla bla bla bla. (very long)
'opening the datasource and importing the data
oApp.ActiveDocument.MailMerge.OpenDataSource "C:\windows\desktop\AIS-010731-1-K-BE.mdb", Connection:=Constr, SQLStatement:=strSQL
'end of code
****************************
thanks
