By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Creating link tables to SQL luanches dialog box

P: n/a
We have a old VBA app that create 3 linked tables to SQL server.
However for whatever reason now whenever the code tries to create these
linked tables, a dialog box comes up prompting for a server name and
with the username and password already filled. Anyone know why these
dialog boxes are coming up? 3 linked tables means 3 prompts. Is there a
problem with the DSN?

Here is the code that creates the problems:

sConnect = "ODBC;Driver={SQL Server};Database=" + sDB + ";" & "UID=" +
sUID & ";" & "PWD=" & sPWD & ";" & "DSN=" & sDSN

Set tblTableDef = CurDb.CreateTableDef("NextAvailableCode")
tblTableDef.Connect = sConnect
tblTableDef.SourceTableName = "NextAvailableCode"
CurDb.TableDefs.Append tblTableDef 'dialog box comes up here

Set tblTableDef = CurDb.CreateTableDef("IndustryClass")
tblTableDef.Connect = sConnect
tblTableDef.SourceTableName = "IndustryClass"
CurDb.TableDefs.Append tblTableDef 'dialog box comes up here

Set tblTableDef = CurDb.CreateTableDef("OtherClass")
tblTableDef.Connect = sConnect
tblTableDef.SourceTableName = "OtherClass"
CurDb.TableDefs.Append tblTableDef 'dialog box comes up here

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
This is how the DSN is being created

sAttribs = "Description=This Source contains " & _
"FundWorx Data." & vbCr
sAttribs = sAttribs & "Server=" & sServer & vbCr
sAttribs = sAttribs & "Database=" & sDB
DBEngine.RegisterDatabase DSN:="Connect to ODBC SQL", _
Driver:="SQL Server", silent:=True, _
Attributes:=sAttribs

Nov 13 '05 #2

P: n/a
>>
sConnect = "ODBC;Driver={SQL Server};Database=" + sDB + ";" & "UID=" +
sUID & ";" & "PWD=" & sPWD & ";" & "DSN=" & sDSN

Set tblTableDef = CurDb.CreateTableDef("NextAvailableCode")
tblTableDef.Connect = sConnect
tblTableDef.SourceTableName = "NextAvailableCode"
CurDb.TableDefs.Append tblTableDef
<<

Change connection string to this:

sConnect = "ODBC;Driver={SQL Server};Server=<your Sql Server
Name>;Database=" + sDB + ";" & "UID=" + sUID & ";" & "PWD=" & sPWD & ";"
& "DSN=" & sDSN & ";"

I added ->Server=server1<- (for example) to the string above. I also
added an ending semicolog after sDSN

Then, you need to add the owner name to the sourceTableName:

dbo.NextAvailableCode

and make sure that this table exists in Sql Sever. If the table
"NextAvailableCode" does exist, the preface it with dbo. and use the
connection string above. If that table does not exist in Sql Server
then use a table which does exist in sql server. This should eliminate
the prompts.

I would try this in a standard code module with just one table for
testing. Make sure that "NextAvailableCode" does not already exist in
Access Tables window, either. Delete it if it does exist. Then add
your new code to a sub for just one table and run that. Hopefully, this
will work for you.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #3

P: n/a
Rich you are the man!!!!

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.