dsn less code

[Ngan Bui]

I tried the code from Douglas Steele's page:
http://members.rogers.com/douglas.j.steele/DSNLessLinks.htm
l

I get this error message when running the code from the
immediate window:

Compile Error:
Expected: =

The server name is "Teritus\SQL" and the database name
is "ComplaintsdataSQL".

I'm running Office XP on Windows 2000 with a SQL server
2000. I have already upsized the mdb to a adp file using
the upsizing wizard...upsized everything, tables, queries,
forms, reports.

 

[MGFoster]

I tried the code from Douglas Steele's page:
http://members.rogers.com/douglas.j.steele/DSNLessLinks.htm
l

I get this error message when running the code from the
immediate window:

Compile Error:
Expected: =

The server name is "Teritus\SQL" and the database name
is "ComplaintsdataSQL".

I'm running Office XP on Windows 2000 with a SQL server
2000. I have already upsized the mdb to a adp file using
the upsizing wizard...upsized everything, tables, queries,
forms, reports.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Using ODBC, the DSN-less connect string would be:

"ODBC;Driver={SQL
Server};Server=Teritus\SQL;Database=ComplaintsdataSQL;Trusted_Connecti
n=Yes"

Beware of line wrap. This string should be all one line. There is a
space between "SQL" and "Server" in the Driver section of the line.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFD7WIechKqOuFEgEQLSIACdE9gb0Bk40Ie/+pzMTD9WeAgOrIsAnR23
9YdCLb+7Rw+XMitOXekYmeys
=h7mz
-----END PGP SIGNATURE-----

 

[Ngan Bui]

Using ODBC, the DSN-less connect string would be:
I did check the function and there isn't a problem with
word wrapping. The sub was
FixConnections"Teritus\SQL", "ComplaintsdataSQL")

This is that part of the sub you were talking about:

' Create a new TableDef object, using the DSN-less
connection

Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables
(intLoop).TableName)
tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE="
& _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"
tdfCurrent.SourceTableName = typNewTables
(intLoop).SourceTableName
dbCurrent.TableDefs.Append tdfCurrent

Then in the Immediate Window, I typed:
FixConnections"Teritus\SQL", "ComplaintsdataSQL")

and clicked Enter and I got that error.

Did I miss something?

=======

Mcfoster wrote:

"ODBC;Driver={SQL
Server};Server=Teritus\SQL;Database=ComplaintsdataSQL;Trust
ed_Connecti
n=Yes"

Beware of line wrap. This string should be all one line.
There is a
space between "SQL" and "Server" in the Driver section of
the line.

- --

 

[MGFoster]

Using ODBC, the DSN-less connect string would be:
I did check the function and there isn't a problem with
word wrapping. The sub was
FixConnections"Teritus\SQL", "ComplaintsdataSQL")

This is that part of the sub you were talking about:

' Create a new TableDef object, using the DSN-less
connection

Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables
(intLoop).TableName)
tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE="
& _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"
tdfCurrent.SourceTableName = typNewTables
(intLoop).SourceTableName
dbCurrent.TableDefs.Append tdfCurrent

Then in the Immediate Window, I typed:
FixConnections"Teritus\SQL", "ComplaintsdataSQL")

and clicked Enter and I got that error.

Did I miss something?

=======

< snip >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It may be that the server name "Tertius\SQL" is causing problems 'cuz,
maybe, SQL'r or ODBC doesn't like the "\" in the connection string.
Try "Tertius\\SQL" 'cuz that "escapes" the "\" so only one "\" will
exist in the string (not sure).

Another thought: If you're not connecting to the server using NT
security, but SQL sign-on security, you'll need to include the
password and user id in the connection string. E.g.:

...;PWD=password;UID=userID;...

and remove the ";Trusted_Connection=yes" parameter.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFItJYechKqOuFEgEQIhvwCeJg3mxGtt+2HZ9pEJyK8palS+cmIAn0VK
f80r20y20R6xz76rCYJGd/Vz
=a/xC
-----END PGP SIGNATURE-----

 

[Ngan Bui]

Ok,
in the immediate window, I type:

Call FixConnections("Teritus\\SQL", "ComplaintsdataSQL")

I get a different error:

Item not found in this collection. (3265) encountered.

I did it with Teritus\SQL too and it errored out.

Ngan

 

[MGFoster]

Ok,
in the immediate window, I type:

Call FixConnections("Teritus\\SQL", "ComplaintsdataSQL")

I get a different error:

Item not found in this collection. (3265) encountered.

I did it with Teritus\SQL too and it errored out.

Ngan



problems 'cuz,


connection string.


one "\" will


using NT


include the

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Looks like it is not "seeing" the SQL server. If you know the IP
address of the server try that instead. E.g.:

;Server=197.0.0.1;Database=

For your reconnect function you might wish to look at this. It is one
I use when developing DSN-less tables (uses DAO). The table already
exists. It usually starts out as a DSN linked table. This Sub turns
it into a DSN-less table. Then the front-end is ready to distribute
to the users.

Sub ReconnectTable(strTable As String)

' Uses NT log-in security.
Const ODBC_CONN = "ODBC;DRIVER=SQL Server;" & _
"SERVER=SERVERName;" & _
"DATABASE=DBName;" & _
"Trusted_Connection=Yes"

Dim td As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set td = db.TableDefs(strTable)
td.Connect = ODBC_CONN
td.RefreshLink

Debug.Print "Done"

On Error Resume Next
Set td = Nothing
Set db = Nothing

End Function

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFJ6goechKqOuFEgEQKEMACfe9K54D5jhxK3aigl5H7jeeqFX4gAoO7h
kaKi5AH6fBHScGWWUQnUYe7m
=x2hu
-----END PGP SIGNATURE-----

 

[Richard Vaughns]

Dear MGFoster

In an instance such as you have encountered, it is always a good idea to
re-boot.