473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Creating link tables to SQL luanches dialog box

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
3 1753
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
>>
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
Rich you are the man!!!!

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

24
by: jonathon | last post by:
Hi all, I have a web app with a popup window for entering data. I don't want to access the web every time this window is opened, as most of the app is AJAX. But I can't figure out how to open...
31
by: Neil | last post by:
I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 back end. I currently have a selections table in the front end file which the users use to make selections of records. The table...
4
by: L Mehl | last post by:
Hello -- I am using Enterprise Architect, a UML modeling tool, which is built on a pre-Access 2000 mdb. The database describing the model has the extension ".eap". I want to build some...
2
by: RWC | last post by:
Hello, I'm trying to do some updates on the fly in a remote database. I'd like to do this with code. I'm running basically the following code; Dim db As Database Dim tblDefs As TableDefs...
8
by: Brian S. Smith | last post by:
Hi gang, Please help. I've been through the Access help, searched the Web, and I can't seem to get a straight answer. As the Subject line suggests, I want to run a fairly simple VB/Access Sub...
7
by: Lisa | last post by:
I have an Access 2000 application that uses the following function to re-link my tables when I switch from my Current back end to a Dummy back end. I also use it to refresh my links. Function...
7
by: ApexData | last post by:
Hello I currently Link the FE/BE using the LinkTables Option and the Linked Table Manager. Any time I need to move the BE to another location, I have to go through this process over again. I...
6
by: Matt | last post by:
Hi All, I have found a version of my answer on GG, but I have not been able to find my exact answer, thus the post. I am using Access 2000 Situation: I have a multi user frontend database...
8
by: Neil | last post by:
I just started using Access 2003, and I can't link a SQL Server table. I right-click on the database window; select Link Tables; and select ODBC Databases from the Files of Type dropdown. As soon...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.