sql too long? please help!

[Joep1st]

last time unfortunely no response. hopefully now better.
thanks a lot!

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&quot
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

 

[Taff07]

Can you post the actual Sql? Have you tried changing the names to aliases within the sql if you keep having to repeat the Names?

Ian

 

[Joep1st]

thanks a lot for the initiative! The Table names are mosly in Dutch, so I don't think it makes sence for you;-). However, you Can look at the structure and possible solutions.



SQL:
******************************8

SELECT TblOffertes.OfferteID, TblBedrijven.Bedrijfnaam, TblContactpersonen.CPGeslacht, IIf([CPgeslacht]='man',"De heer",IIf([CPgeslacht]='vrouw',"Mevrouw","Mevrouw / De heer&quot) & ' ' & Left([CPVoornaam],1) & '.' AS [aanhef_+voorlettter], TblContactpersonen.CPTussenvoegsel, TblContactpersonen.CPAchternaam, TblBedrijfAdressen.AdresStraat, TblBedrijfAdressen.AdresHuisnummer, TblBedrijfAdressen.AdresStraatPostcode, TblBedrijfAdressen.AdresPostbus, TblBedrijfAdressen.AdresPostbusPostcode, TblBedrijfAdressen.AdresPostbusPlaats, TblBedrijfAdressen.AdresPostbusLand, TblBedrijfAdressen.AdresPlaats, TblPersoneel.PersoneelVoornaam, TblPersoneel.PersoneelTussenvoegsel, TblPersoneel.PersoneelAchternaam, TblPersoneel.PersoneelFunctie
FROM (((TblOffertes LEFT JOIN TblBedrijven ON TblOffertes.BedrijfID = TblBedrijven.BedrijfID) LEFT JOIN TblContactpersonen ON TblOffertes.ContactPersoonID = TblContactpersonen.ContactpersoonID) LEFT JOIN TblPersoneel ON TblOffertes.Eindverantwoordelijk = TblPersoneel.PersoneelID) LEFT JOIN TblBedrijfAdressen ON TblOffertes.AdresID = TblBedrijfAdressen.AdresID
WHERE (((TblOffertes.OfferteID)="1&quot);

 

[Craig0201]

Create a query based upon your SQL and call the query.

Craig

 

[Joep1st]

is it possible to do so, with only a temporarely query?
so only as a object in the memory and not in the actual query list in access? I don't want to make a mess with a lot of queries I never use again.
thanks!

 

[pcdaveh]

You could embed your query in you code. You could also use a query.def to define the query in code also. It becomes a logical query and not a member of any list.