Create DSN in Windows 7

[r15gsy]

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?

 

[dhookom]

Have you considered using DSN-less connections? You might want to look at Doug Steele's page.

I just spoke with him last week and he suggested he needs to update the page.

Duane
Hook'D on Access
MS Access MVP

 

[r15gsy]

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.

 

[dhookom]

What driver/backend are you attempting to use?

Duane
Hook'D on Access
MS Access MVP

 

[r15gsy]

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:

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!

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"

 

[dhookom]

Sorry I can't be of greater assistance since I don't have anything to test.

Since you haven't told us what you have tried, did you consider Register Database?

Duane
Hook'D on Access
MS Access MVP

 

[Goondu]

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.

 

[vbajock]

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