Password not being saved when I create a link to a SQL table

  • Thread starter Thread starter Stacey Levine
  • Start date Start date
S

Stacey Levine

My code is below. I am moving tables from Access to SQL. This code is run
(because many databases did link to a table residing in Access), from part
of a program that has determined what access databases are using my table.
Everything works correctly, except it is not saving the password with the
newly linked table. So when I go into access to check the table, I have to
enter the password .. to get to the data. Any ideas what is wrong? Thanks.

Stacey


Private Sub RelinkTable(WhatDB As String)
Dim MyDB As Database
Dim NewLink As String
Dim dbs As DAO.Database
Dim dbe As DAO.DBEngine
Dim tDef As DAO.TableDef

NewLink = "ODBC;DSN=MerchData;UID=MyUser;PWD=MyPassword;DATABASE=Merch"
Set dbe = New DAO.DBEngine

Me.lblStatus.Caption = "Change database " & WhatDB
Me.lblStatus.Refresh
Set dbs = dbe.OpenDatabase(WhatDB)
dbs.TableDefs.Delete (Me.txtTableName.Text)

Set tDef = dbs.CreateTableDef
tDef.Connect = NewLink
tDef.SourceTableName = Me.txtSqlName.Text
tDef.Name = txtSqlName.Text

dbs.TableDefs.Append tDef
dbs.Close

CloseandLeave:

Set tDef = Nothing
Set dbs = Nothing
Set dbe = Nothing
 
-----Original Message-----
My code is below. I am moving tables from Access to SQL. This code is run
(because many databases did link to a table residing in Access), from part
of a program that has determined what access databases are using my table.
Everything works correctly, except it is not saving the password with the
newly linked table. So when I go into access to check the table, I have to
enter the password .. to get to the data. Any ideas what is wrong? Thanks.

Stacey


Private Sub RelinkTable(WhatDB As String)
Dim MyDB As Database
Dim NewLink As String
Dim dbs As DAO.Database
Dim dbe As DAO.DBEngine
Dim tDef As DAO.TableDef

NewLink
Click to expand...
= "ODBC;DSN=MerchData;UID=MyUser;PWD=MyPassword;DATABASE=Me
rch"
Set dbe = New DAO.DBEngine

Me.lblStatus.Caption = "Change database " & WhatDB
Me.lblStatus.Refresh
Set dbs = dbe.OpenDatabase(WhatDB)
dbs.TableDefs.Delete (Me.txtTableName.Text)

Set tDef = dbs.CreateTableDef
tDef.Connect = NewLink
tDef.SourceTableName = Me.txtSqlName.Text
tDef.Name = txtSqlName.Text

dbs.TableDefs.Append tDef
dbs.Close

CloseandLeave:

Set tDef = Nothing
Set dbs = Nothing
Set dbe = Nothing




.
Click to expand...

Ok, so what is it exactly that you want the code to do?
Are you running this code from the same Access DB where
the tables are or is it on a different database with
linked tables. You said you are moving Tables to SQL. How
are you moving them, and what does the above code exactly
accomplish?
 
The code is part of a larger program. I have already physically moved the
table into SQL. This code drops the table out of Access and then creates a
link in access to the table that now resides in SQL.
 
-----Original Message-----
The code is part of a larger program. I have already physically moved the
table into SQL. This code drops the table out of Access and then creates a
link in access to the table that now resides in SQL.
Click to expand...
-- So this code DELETES the existing table in MS Access
then tries to Link to it using the ODBC string you are
providing and that table now resides on SQL server?
Anyway, I'd say you have to check your DNS settings. I
don't think you should try to specify the password in code
when you have a DNS already setup. Why don't you give a
DNS-less connection a try instead:

cnnStr = "driver={SQL Server};server=myserver;" & _
"database=mydb;Username=<username>;PWD=<strong
password>;dsn=;"
Set cn = en.OpenConnection("", False, False, cnstr)
 
The DSN less works fine. I liked using the DSN because I can take the
access database and point it to a test database by just chaing one thing.
I guess I will have to go with the DSN less approach. thanks

stacey
 
Glad it worked. I understand now what you were trying to
do, simplify the connection so that you can only change
one thing if you wanted to connect to different DBs. DNS-
less will almost do that, and if your test DBs can be
accessed by same user name and password and sit on the
same server, then all you have to do is change the
database name

cheers
 
Back
Top