ODBC Connection Issue on Client Site

Hi,

It might be a basic question but could you please help me on that?

Working Environment:

I have developed application on SQL Server 2005 and now wants to Run on Client Site, I am using access as a front end and SQL server 2005 back end through ODBC (System Dsn) which is working good. (user has been added on SQL Server and on Database)

Question:

1) Do I need to create new System Dsn Or other DSN (Keep in mind I have already create System DSN in Server) at each client site to access application from Server?

(If yes then I am trying to do that, it allow me to create (with required database which is on Server) and verify but doesn’t save on ODBC list so I can’t use that name to run appliation on client site)

I am using windows authentication.

Thanks for help in advance

It's done the issue was admin rights on the client's computer.

ODBC Connection

I have front-end visual basic program that use an ODBC connection to connect with SQL server, and my questions is there any automated procedure to configure this ODBC connection instead of configure it manually specially of the user profiles was changed I have to configure it again.

Please advise

YOu either could use a connection string for connecting instead of using a dsn, or you use an UDL file which is already configured for the appropiate parameters.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

As Jens said, you could use a DSN. An example of using this via SQLConnect( ) is on:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlconnect.asp.

Regardless of your connection method, you won't be able to store your user ID and password in the DSN. This would be a great security risk anyway. You could make the connection use a trusted connection, and rely on NT authentication to validate the user.

|||

Mohamed Hussein Kotat wrote:

I have front-end visual basic program that use an ODBC connection to connect with SQL server, and my questions is there any automated procedure to configure this ODBC connection instead of configure it manually specially of the user profiles was changed I have to configure it again.

Please advise

Try with string "ODBC; DSN=name_dsn; UID=sa; PWD=password_sa; DATABASE=database_name"

ODBC Connection

I have front-end visual basic program that use an ODBC connection to connect with SQL server, and my questions is there any automated procedure to configure this ODBC connection instead of configure it manually specially of the user profiles was changed I have to configure it again.

Please advise

YOu either could use a connection string for connecting instead of using a dsn, or you use an UDL file which is already configured for the appropiate parameters.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

As Jens said, you could use a DSN. An example of using this via SQLConnect( ) is on:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlconnect.asp.

Regardless of your connection method, you won't be able to store your user ID and password in the DSN. This would be a great security risk anyway. You could make the connection use a trusted connection, and rely on NT authentication to validate the user.

|||

Mohamed Hussein Kotat wrote:

I have front-end visual basic program that use an ODBC connection to connect with SQL server, and my questions is there any automated procedure to configure this ODBC connection instead of configure it manually specially of the user profiles was changed I have to configure it again.

Please advise

Try with string "ODBC; DSN=name_dsn; UID=sa; PWD=password_sa; DATABASE=database_name"

ODBC Connection

I have front-end visual basic program that use an ODBC connection to connect with SQL server, and my questions is there any automated procedure to configure this ODBC connection instead of configure it manually specially of the user profiles was changed I have to configure it again.

Please advise

YOu either could use a connection string for connecting instead of using a dsn, or you use an UDL file which is already configured for the appropiate parameters.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

As Jens said, you could use a DSN. An example of using this via SQLConnect( ) is on:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlconnect.asp.

Regardless of your connection method, you won't be able to store your user ID and password in the DSN. This would be a great security risk anyway. You could make the connection use a trusted connection, and rely on NT authentication to validate the user.

|||

Mohamed Hussein Kotat wrote:

I have front-end visual basic program that use an ODBC connection to connect with SQL server, and my questions is there any automated procedure to configure this ODBC connection instead of configure it manually specially of the user profiles was changed I have to configure it again.

Please advise

Try with string "ODBC; DSN=name_dsn; UID=sa; PWD=password_sa; DATABASE=database_name"

ObjRecordSet somehow not populating in ActiveX Script

'************************************************* ***********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Option Explicit
Function Main()
Dim objConnection
Dim objRecordSet
Dim strTotal
Dim strPathtoTextFile
DTSGlobalVariables("sSub").Value = "Errors: = "
strtotal = strtotal & DTSGlobalVariables("sSub").Value & "0"
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
strPathtoTextFile = "M:\Rep\AA\"
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=NO;FMT=Delimited"""
objRecordset.Open "SELECT * FROM svcmont.txt", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
Wscript.Echo "Ty: " & objRecordset.Fields.Item("Ty")
Wscript.Echo "Va: " & objRecordset.Fields.Item("Va")
objRecordset.MoveNext
Loop
DTSGlobalVariables("sSub").Value = DTSGlobalVariables("sSub").Value &
objRecordset.Fields.Item("Va")
If strtotal <> DTSGlobalVariables("sSub").Value Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
'************************************************* ***********************
Sample of input file:
Q, 0
QC, 0
Q, 0
Q, 0
Q, 0
QC, 0
Is this a replication script? I think you should repost this in the DTS
group.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Swim" <crombusch@.gmail.com> wrote in message
news:1138049022.892789.174050@.g43g2000cwa.googlegr oups.com...
> '************************************************* ***********************
> ' Visual Basic ActiveX Script
> '************************************************* ***********************
> Option Explicit
> Function Main()
> Dim objConnection
> Dim objRecordSet
> Dim strTotal
> Dim strPathtoTextFile
> DTSGlobalVariables("sSub").Value = "Errors: = "
> strtotal = strtotal & DTSGlobalVariables("sSub").Value & "0"
> On Error Resume Next
> Const adOpenStatic = 3
> Const adLockOptimistic = 3
> Const adCmdText = &H0001
> Set objConnection = CreateObject("ADODB.Connection")
> Set objRecordSet = CreateObject("ADODB.Recordset")
> strPathtoTextFile = "M:\Rep\AA\"
> objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & strPathtoTextFile & ";" & _
> "Extended Properties=""text;HDR=NO;FMT=Delimited"""
> objRecordset.Open "SELECT * FROM svcmont.txt", _
> objConnection, adOpenStatic, adLockOptimistic, adCmdText
> Do Until objRecordset.EOF
> Wscript.Echo "Ty: " & objRecordset.Fields.Item("Ty")
> Wscript.Echo "Va: " & objRecordset.Fields.Item("Va")
> objRecordset.MoveNext
> Loop
> DTSGlobalVariables("sSub").Value = DTSGlobalVariables("sSub").Value &
> objRecordset.Fields.Item("Va")
> If strtotal <> DTSGlobalVariables("sSub").Value Then
> Main = DTSTaskExecResult_Success
> Else
> Main = DTSTaskExecResult_Failure
> End If
> End Function
> '************************************************* ***********************
> Sample of input file:
> --
> Q, 0
> QC, 0
> Q, 0
> Q, 0
> Q, 0
> QC, 0
>