dpeadmin
asked on
connecting to SQL Server 2000 databse with word 2000 VBA using ODBC
Hi
I've created an ODBC connection to my server, and specified the database I wsh to use. It tests sucessfully.
Now I would like ot use word vba to connect to a view in this database and pull data where the field NT_username is equal to environ("Username").
this is the code I am using in Sub document_Open() :
Dim strSQL As String
strSQL = "Select * from En_Contacts_220708 where NT_Username=" & Environ("Username")
ActiveDocument.MailMerge.O
ActiveDocument.MailMerge.E
I have tried playing around with the text here& changing the 2 instances of Enhancements to Enhancements.dsn but this makes no difference.
the error I get is:
'Data Source name not found and no default driver specified.
Can anyone help?
Thanks
I've created an ODBC connection to my server, and specified the database I wsh to use. It tests sucessfully.
Now I would like ot use word vba to connect to a view in this database and pull data where the field NT_username is equal to environ("Username").
this is the code I am using in Sub document_Open() :
Dim strSQL As String
strSQL = "Select * from En_Contacts_220708 where NT_Username=" & Environ("Username")
ActiveDocument.MailMerge.O
ActiveDocument.MailMerge.E
I have tried playing around with the text here& changing the 2 instances of Enhancements to Enhancements.dsn but this makes no difference.
the error I get is:
'Data Source name not found and no default driver specified.
Can anyone help?
Thanks
Last Comment
ASKER
I've tried that but no joy. I get the same error, when I click ok I get a VBA error:
runtime error '5922'
Word was unable to open the datasource
Would it be something to do with uid and pwd connecting from word so it does not use my credentials?
runtime error '5922'
Word was unable to open the datasource
Would it be something to do with uid and pwd connecting from word so it does not use my credentials?
try example:
from
http://msdn.microsoft.com/en-us/library/aa140183(office.10).aspx
ActiveDocument.MailMerge.O
--When using SQL Server as a data source, the field content
from
http://msdn.microsoft.com/en-us/library/aa140183(office.10).aspx
ActiveDocument.MailMerge.O
--When using SQL Server as a data source, the field content
yes - looks like it needs UID\PWD
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Do you know how to create DSN.
ASKER
EugeneZ
When I use that code in the Word window I get a 'Confirm Data Source' dialog box with options like, for example, Excel Files via ODBC - but no option for Sql Svr. When I cancel the 'Confirm Data Source' dialog box in the VBA window I get an error:
runtime error '4198': Comman failed
MuhammadKashif
I created a file DSN through the ODBC in Admin tasks of Control Panel, I have done this before but not for the reason of connecting from Word 2000 - When I test the connection after creating it, the test is successful.
When I use that code in the Word window I get a 'Confirm Data Source' dialog box with options like, for example, Excel Files via ODBC - but no option for Sql Svr. When I cancel the 'Confirm Data Source' dialog box in the VBA window I get an error:
runtime error '4198': Comman failed
MuhammadKashif
I created a file DSN through the ODBC in Admin tasks of Control Panel, I have done this before but not for the reason of connecting from Word 2000 - When I test the connection after creating it, the test is successful.
try to add
ConfirmConversions:=False,
ConfirmConversions:=False,
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Guys,
This person who opened this question is no longer working with us therefore the question is no longer valid.
Sorry she didn't comment/close the question before she left, I believe she did get the connectivity working.
This person who opened this question is no longer working with us therefore the question is no longer valid.
Sorry she didn't comment/close the question before she left, I believe she did get the connectivity working.
ASKER
Awarding points on behalf of person who posted question. Unsure of outcome of answers but am awarding as a matter of courtesy
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
like "DSN="DSN=Enhancements",