We help IT Professionals succeed at work.

ADO Connection issue

Ronald popo
Ronald popo asked
on
Hi everyone,
I have a word template that merges data from a sql DB.  I did not write it.  The server crashed and a new server was installed.  I am having a connection problem.  It is a template that was written prior to 2005.

This is part of the code concerning the connection

Public drive As New ADODB.Connection
Public cmd As New ADODB.Command
Public DOTainCntFlag As String

in addition:

Public Sub GetLPMain()
On Error GoTo Err_GetLDriving
Dim sqlstring As String
Dim lpmrs As New ADODB.Recordset
'
' Connects to database and gets Driversrecord
'

Customer_num = [custNumber]

' Set ADO connection
drive.Open "DSN=myserver-new-year\Drivers;uid=workers;pwd=checking;database=Driving"
Set cmd.ActiveConnection = drive

I'm getting the following error.  I've googled it and still the same

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
Comment
Watch Question

View Solution Only

ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:

Look at this and choose some of the options suitable for you:

https://www.connectionstrings.com/sql-server/




ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:

Sorry, you have to change your local ODBC DSN settings, not the connection string.


Open this Exe and look for your DSN Name and change the Config for your DSN entry:

C:\Windows\sysWOW64\odbcad32.exe



ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:

Also check if you do not have 64bit Windows this ODBC Manager to find your DSN entry:

C:\Windows\system32\odbcad32.exe




"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
"DSN" means you need to set up an ODBC connection with that name, as stated above. But that DSN looks strange.

Probably the correct connection string would not require an ODBC Datasource definition, The syntax shown points towards SQL Server as driver, so you would use
Driver={SQL Server}; Server=myserver-new-year\Drivers; uid=workers; pwd=checking; database=Driving

Open in new window

for ODBC or
Provider=SqlOleDB; Server=myserver-new-year\Drivers; uid=workers; pwd=checking; database=Driving

Open in new window

for OLE DB.
Ronald popodeveloper

Author

Commented:
below is my string and I'm getting command failed error[

WordBasic.MailMergeMainDocumentType 0
WordBasic.MailMergeOpenDataSource Name:="", ConfirmConversions:=0, ReadOnly:=0, LinkToSource:=0, AddToMru:=0, PasswordDoc:="", PasswordDot:="", Revert:=0, WritePasswordDoc:="", WritePasswordDot:="", Connection:="Driver={SQL Server};Server=myserver-new-year\Drivers;UID=empnum1;PWD=;APP=Microsoft Query;WSID=myserver-new-year\Drivers;LANGUAGE=us_english;DATABASE=Driving", SQLStatement:="execute splp_empcust " & Chr(34) & custnumbers & Chr(34), SQLStatement1:=""
WordBasic.MailMerge CheckErrors:=1, Destination:=0, MergeRecords:=0, From:="", To:="", Suppression:=0, MailMerge:=1, MailSubject:="", MailAsAttachment:=0, MailAddress:=""
ZvonkoSystems architect
CERTIFIED EXPERT
Top Expert 2006

Commented:

Please write more details here on EE.


Nobody can look at your script lines and guess what is your environment and what is going wrong without  error message details.


Therefor post here your complette error message and state whether you use ODBC DSN or now you try to connect without local ODBC manager.



Ronald popodeveloper

Author

Commented:
Thank you gentlemen.  This original question is working.  Thanks again.