Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create DSN in Windows 7

Status
Not open for further replies.

r15gsy

IS-IT--Management
Jan 9, 2008
22
Hi

I am trying to create a DSN entry in ODBC Manager for several database connections.

I have google'd and used various coding, including Microsoft technet support. But I cannot get any to work.

I think the problem is I am running Windows 7 64 bit and the ODBC version is different.

Any help?
 
Thanks for the reply.

I have tried to create DSN-less, that would be my preferred option.

But the ODBC driver supplied is not documented anywhere and I cannot get it to connect DSN-less. I posted last week looking for help on that, as I can get it to work through ADO but not DAO. I get a reserved error 3000 (-7778) and I cannot find a solution on google.

It works fine with DSN setup, hence why I was looking to simply create the DSN on the fly instead.
 
Hi

I am using an accounts package called "Sage Line 50" with their own bespoke ODBC driver which is installed with the package.

I have interrogated the DSN settings which are very simple:

DRIVER = {Sage Line 50 v15}
DIR = c:\sage\accounts\
UID = username
PWD = password

Like I say I can connect if I setup a DSN on the system. And I can connect with the following ADO connection:

Code:
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim sConnString  As String
Dim strSQL As String

sConnString = "Provider=MSDASQL.1;DRIVER={Sage Line 50 v15};UID=username;PWD=password;DIR=c:\sage\accounts"

conn.Open sConnString
Set cmd.ActiveConnection = conn

cmd.CommandText = "SELECT * FROM AUDIT_JOURNAL"

cmd.CommandType = adCmdText
Set rs = cmd.Execute

Do While Not rs.EOF
 
Debug.Print rs.Fields("DATE")
rs.MoveNext
Loop

Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

But this code doesn't work with the DSN-less connection, but does if I change to DSN with same code. It does connect, becuase I get other errors (such as incorrect password / cannot locate all data files) but gives me reserved error 3000 (-7778) which is no use!

Code:
Dim strODBC As String

'strODBC = "ODBC;Provider=MSDASQL.1;DRIVER={Sage Line 50 v15};UID=username;PWD=password;DIR=c:\sage\accounts"
strODBC = "ODBC;Provider=MSDASQL.1;DSN=SAGEACA;UID=username;PWD=password"

DoCmd.TransferDatabase acImport, "ODBC Database", strODBC, acTable, "AUDIT_JOURNAL", "ACA_AUDIT_JOURNAL"
 
r15gsy,

Check for "Sage Line 50 V15" for the latest 64-bit? Look for Sage site for Win7 I guess. Or...was the driver installed and registered correctly?

I can't help as I don't use Sage driver.
 
strODBC = "ODBC;Provider=MSDASQL.1;DSN=SAGEACA;UID=username;PWD=password"

This may be a dumb question, but you're not really using username and password as the username and password, are you?

Put a Stop or a breakpoint into your code after the line strOdbc = etc. When it hits the Stop

Open the Immediate Window and typein

?strODBC

Hit enter

and see if you are passing it valid credentials.

If your username and pwd are valid, next I'd try a different way of opening the table, not all ODBC drivers recognize the Command object. I'd do this:


Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim strSQL As String

sConnString = "Provider=MSDASQL.1;DRIVER={Sage Line 50 v15};UID=username;PWD=password;DIR=c:\sage\accounts"

conn.Open sConnString


strSQL = "SELECT * FROM AUDIT_JOURNAL"
Set rs=new ADOB.recordset
rs.Open strSQL,conn, adOpenDynamic, adLockOptimistic, adCmdText

Do While Not rs.EOF

Debug.Print rs.Fields("DATE")
rs.MoveNext
Loop

Set rs = Nothing
conn.Close
Set conn = Nothing



 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top