Setting up DSN through Code

  • Thread starter Thread starter Mark A. Sam
  • Start date Start date
M

Mark A. Sam

I am considering writing and distributing a small Access app which links to
an SQL database through the internet and wonder how the application can set
up a DSN on the users machine and link the necessary tables. Is there some
code that someone can give me to accomplish this?

Thanks and God Bless,

Mark A. Sam
 
You can make sure a reference is set to the DAO object library and use the
RegisterDatabase method. You can also use vbscript to create DSNs by
creating registry entries.
 
Hello Doug,

Are you saying your procedure will address a remote server through the
internet? Who does it know where the server is located?

God Bless,

Mark
 
All I'm saying is that if you can connect using a DSN, you almost certainly
can connect using a DSN-less connection string.

Take a look at Carl Prothman's site (there's a link to it on my page)
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer

He says to connect to SQL Server running on a remote computer (via an IP
address), use

oConn.Open "Driver={SQL Server};" & _
"Server=xxx.xxx.xxx.xxx;" & _
"Address=xxx.xxx.xxx.xxx,1433;" & _
"Network=DBMSSOCN;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

Where:
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.
- "Network=DBMSSOCN" tells ODBC to use TCP/IP rather than Named

That means you'd need to modify the function declaration in my code from

Sub FixConnections(ServerName As String, DatabaseName As String)

to something like

Sub FixConnections( _
ServerName As String, _
DatabaseName As String, _
IPAddress As String, _
UserName As String, _
Password As String _
)

and the section

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"

to something like

tdfCurrent.Connect = "ODBC;Driver={SQL Server};" & _
"Server=" & ServerName & ";" & _
"Address=" & IPAddress & ",1433;" & _
"Network=DBMSSOCN;" & _
"Database=" & DatabaseName & ";" & _
"Uid=" & Username & ";" & _
"Pwd=" & Password & ";"
 
Doug,

The connect property doesn't reference an IP address, and thus can't locate
the server. I don't know if what I am seeking can be accomplished with a
DSN-less connection.

God Bless,

Mark
 
So if I were to use SQL Server for the backend for a website, I could
connect to the db using an Access frontend on my laptop?
 
I've never tried to connect to a SQL Server on the web, but theoretically
using the connection string I posted elsewhere in this thread should work.
 
How does the DSN know where the database is, then, if it doesn't use an IP
address?

DSNs are strictly registry entries. Take a look in either
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI or
HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI (depending on whether it's a System
or User DSN) and see what it's actually got there.
 
David said:
So if I were to use SQL Server for the backend for a website, I could
connect to the db using an Access frontend on my laptop?
Click to expand...

If the port that ODBC uses were left open to the internet, yes. That is not a
recommended approach for security reasons though.
 
I thought there might be some security issues (it is afterall a
Microsoft product). What then is the recommended approach?
 
David C. Holley said:
I thought there might be some security issues (it is afterall a Microsoft
product). What then is the recommended approach?
Click to expand...

Apps that run in web browsers mostly. As others have stated you can use
remote session software with VPNs if your target user audience is fairly
small.
 
Rick,

Is a table linked to an SQL Database through the web a security issue if the
database is open, but not the tables?

God Bless,

Mark
 
Actually, I think that the question at hand is wether or not its
possible to have a linked table to a SQL Server database where the
connection is made securly over the internet. (or in short) Is it
possible to securly connect to a SQLServer database?
 
David,

I am connected to an SQL Database with linked tables. It is liked being
locally connected. Whether it is secure, I don't know. I'd like to know if
just being linked presents security issues.

God Bless,

Mark
 
Mark said:
David,

I am connected to an SQL Database with linked tables. It is liked
being locally connected. Whether it is secure, I don't know. I'd
like to know if just being linked presents security issues.
Click to expand...

The link is irrelevent. Everything on the Access end is irrelevent. The fact
that you CAN link means that the SQL Server port is exposed to the internet and
could potentially be hacked into. How big the threat is I can't say. I only
know that I was forced to re-write an app to use HTTP requests instead of
linking to our SQL Server because of the security threat. Our sys admin had
monitoring tools that showed that our SQL Server port was being "tested"
hundreds of times a week.
 
And that is the same question that I have.
David,

I am connected to an SQL Database with linked tables. It is liked being
locally connected. Whether it is secure, I don't know. I'd like to know if
just being linked presents security issues.

God Bless,

Mark
Click to expand...
 
I was suspecting that that might be an issue - that thanks to MS, its
almost an all or nothing deal. DARN.
 
Did you do anything with replication? I did think about migrating my
website backend to SQLServer and then set things up where a replica sat
 
Rick Brandt said:
The link is irrelevent. Everything on the Access end is irrelevent. The fact
that you CAN link means that the SQL Server port is exposed to the internet and
could potentially be hacked into.
Click to expand...

Thanks for the information, I was worried about that and thought I would
have to rethink my methods. I am on a hosted server, so it isn't my
problem.

How big the threat is I can't say. I only
know that I was forced to re-write an app to use HTTP requests instead of
linking to our SQL Server because of the security threat. Our sys admin had
monitoring tools that showed that our SQL Server port was being "tested"
hundreds of times a week.
Click to expand...

I wonder of Oracle opens a server up to the same threat?
 
Back
Top