ODBC Link With Alias Name...
Sep 27, 2005
I am trying to use one Access 97 mdb file to attach to two different SQL servers depending on the situation, one is production the other is a backup server. (The application requires an Access97 file format so I can't change that) I have a DNS Alias for the server name and I put that in the ODBC under Server Name. When I have the Alias changed from Production to Backup I want the Access file to see the data on the backup box.
It appears that MSAccess stores the Server name when the link is made in the MSysObject.Connect field and it does not update that even, if the ODBC is changed. I have one test connection that seems to working because I created it using the ODBC after the Alias was added. The string it puts in the connect field has no reference to the server name or address similar to what is below.
DSN=A_C;Description=A Control;UID=username;APP=Microsoft® Access;WSID=PC22361;DATABASE=DACSP001;Trusted_Conn ection=Yes
The MSysObject.Connect in a file that did not use the Alias looks like this...
DSN=A_C;Description=A ;APP=Microsoft® Access;WSID=PC27368;DATABASE=DACSP001;Network=DBMS SOCN;Address=pmnt9511.sce.corp.com,1433
So I think I need to recreate a new file using the Alias in the ODBC so the specific address is not in the connection string. What do you think?
Has anyone used an Alias name like I am trying to switch between like databases on two different servers? It seems weird that in the first example above there is not reference to the alias name so maybe this forces it to look it up.
Thanks in advance...
View Replies
ADVERTISEMENT
Feb 19, 2007
Hi.
e.g.
I have 2 tables.
Table A - ODBC LINK From SQL (SQL read only)
ProjectNo
ProjectDescription
ProjectValue
Table B
ProjectNo
Taskgroup
StartDate
EndDate
Comments
I am trying to assign a ProjectNo to a taskgroup, but when I try and join the 2 tables together via query, I am unable to edit information in table 2 (Taskgroup, StartDate etc). The 2 tables are linked via ProjectNo.
I hope this makes sense and someone can help!!
Thanks
Frank.
View 2 Replies
View Related
May 28, 2015
I have big problem with connection between ODBC and Access 2007. Everything is linked correctly but I have problem with separator in decimal field. In my country this separator is "," not ".". I found information about connection in MSysObjects.connect table that
Code:
...;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;...
I found somewhere that I need to change NUM value from NLS (default) to MS. Do you know how to edit this value? For example Access 2007 see value "123.8" (so it is in my country "123,8" ) like "1238"
View 8 Replies
View Related
Jan 26, 2007
Hi Folks.
I'm having a problem with ODBC in Access.
For some unknown reason, when I click Get External Data > Link Tables
the ODBC Dialog box doesn't appear.
I have reinstalled Access and ODBC from the MDAC.
Still the ODBC dialog will not appear.
The funny thing is that existing links to my SQL Server database open fine and display the data.
Has anyone experienced this issue and what was the fix.
Thanks.
View 8 Replies
View Related
Mar 16, 2014
I am trying to create an ODBC link to a copy of the Northwind data base.I am running Win7 on a 64 bit operating system but am keeping office running as 32 bit for Access, Excel , Visio etc due to the fact that I have a lot of old apps tied to them.
When I attempted initially to create a new system DSN ODBC connection it only allowed me to use SQL drivers. (No plurry good) So after some searching I found out that I could use a file in C:WindowsSysWOW64 called "odbcad32.exe" which has allowed me to view /use the total list of drivers to import data.So I can set up a DNS ODBC named Northwind1 and I can access the database without problems using Excel.However if I try to use the same ODBC link in Access to connect to Northwind1 I get the following error message:"You cannot use ODBC to import from export to, or link an external ?Microsoft Access or ISAM database table to your database".
View 2 Replies
View Related
Sep 25, 2007
so, I have created a working SQL statement that summarises data from a bunch of different tables (it's a stock monitoring application so it all has to do with levels of stock, numbers of parts processed etc...):
SELECT tblPPIn.BatchID,
tblPartDescriptions.DrawingNumber AS [Drawing Number],
tblOrder.IssueNumber AS [Issue Number],
tblPartDescriptions.Description AS [Description],
tblPPIn.Qty AS [Total Booked In],
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyProcessed) AS SumOfQtyProcessed FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',(SELECT Sum(tblPPProcessed.QtyProcessed) AS SumOfQtyProcessed FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)) AS [Total Qty Processed],
IIf(IsNull((SELECT Sum(tblPPOut.Qty) As sumofQtyOutPass FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'PASS';)),'0',(SELECT Sum(tblPPOut.Qty) As sumofQtyOutPass FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'PASS';)) AS [Total Good Parts Taken],
IIf(IsNull((SELECT Sum(tblPPOut.Qty) As sumofQtyOutUninspected FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'UNINSPECTED';)),'0',(SELECT Sum(tblPPOut.Qty) As sumofQtyOutUninspected FROM tblPPOut WHERE tblPPOut.BatchID = tblPPIn.BatchID AND tblPPOut.IQCPass = 'UNINSPECTED';)) AS [Total Uninspected Parts Taken],
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyPass) AS SumOfQtyPass FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',(SELECT Sum(tblPPProcessed.QtyPass) AS SumOfQtyPass FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)) AS [Total Passes],
IIf(IsNull((SELECT Sum(tblPPProcessed.QtyFailEtching) + Sum(tblPPProcessed.QtyFailCutOut) + Sum(tblPPProcessed.QtyFailFlatness) + Sum(tblPPProcessed.QtyFailHandling) + Sum(tblPPProcessed.QtyFailOther) AS SumofFails FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;)),'0',((SELECT Sum(tblPPProcessed.QtyFailEtching) + Sum(tblPPProcessed.QtyFailCutOut) + Sum(tblPPProcessed.QtyFailFlatness) + Sum(tblPPProcessed.QtyFailHandling) + Sum(tblPPProcessed.QtyFailOther) AS SumofFails FROM tblPPProcessed WHERE tblPPProcessed.BatchID = tblPPIn.BatchID;))) AS [Total Fails],
[Total Booked In]-[Total Qty Processed]-[Total Uninspected Parts Taken] AS [Total Unprocessed Parts Remaining],
[Total Passes]-[Total Good Parts Taken] AS [Total Good Parts Remaining],
[Total Passes]/[Total Qty Processed] AS [Overall Yield]
FROM tblPPIn INNER JOIN (tblPartDescriptions INNER JOIN tblOrder ON tblPartDescriptions.DrawingNumber = tblOrder.DrawingNumber) ON tblPPIn.BatchID = tblOrder.BatchID
ORDER BY tblPPIn.BatchID ASC;
don't worry about the details, it works as it is above...
now I only want to display data for batches where there are some parts left, so I've tried adding the following WHERE clause:
WHERE ([Total Good Parts Remaining] + [Total Unprocessed Parts Remaining]) <> 0
should work fine, right?
nope, we get the (all too familiar) 'Enter parameter value: Total Good Parts Remaining' (and the same for 'Total Unprocessed Parts Remaining'), despite the fact I have used these aliases in the previous SQL with no problem...
Is there any way around this or do I have to create a big-ass WHERE statement complete with the subqueries I've already used previously?
This is seriously annoying me today... any help would be massively appreciated!
cheers,
Bogzla
View 7 Replies
View Related
Nov 26, 2003
rsQuery.Open "SELECT colorDepth, COUNT(*) AS colorDepthCount FROM px_users GROUP BY colorDepth ORDER BY colorDepthCount, colorDepth", dcnDB, 1, 3
returns the following error:
No value given for one or more required parameters.
Is this just an Access thing, where it won't order by an alias?
View 5 Replies
View Related
May 10, 2007
Hello all,
Does anyone know (and would be willing to share) how one can order the results of a Union query in Access by something other than the returned values?
I have a simple Union query that would work perfectly - if I could get the thing to order the results in a particular order everytime:mad:. The query returns counts (all from one table) of separate select statements that each meet certain conditions (e.g. having state=Nevada, having state=California, etc.) as records - but since Union queries in Access order results in ascending or descending order by returned value, it puts my records in a different order every time the record values change. I need to have them ordered the same way (in the same order as they appear in the Union query, if possible) everytime so that I can return those values to specific cells in MS Excel. Any suggestions would be much appreciated!
View 3 Replies
View Related
Jun 20, 2013
I am writing a very complex 'if statement' query expression. I need to create an alias column based on data from two data fields.
Here's the logic:
True: If [PP] = "WG" or "WS" or "WL" and If [GR] > 10 Then "Skilled" or
[GR] Between 6-9 Then "Semi - Skilled" or [GR] <6 Then "Unskilled"
False: "GS"
View 2 Replies
View Related
Jun 11, 2007
We are using Access 2000 as our front end and SQL 2000 as the back end. We use ODBC to connect to the SQL database. One of my access 2000 programs was getting a little sketchy. So I created and new access 2000 database and imported all the modules, macros, reports, queries and tables. That all worked just fine accept now every time I open the new database it ask me to log in, I think it’s the ODBC connection. I have run table link manager, but it still asked me to log in. I have six other Access databases and none of them ask me to log in. So what am I missing?
View 1 Replies
View Related
Dec 1, 2005
Hi,
Requirement - To generate a report from a remote database
Methods opted -
1. Excel
- Connect remote database via odbc connectivity for the required query
- Excel generates the report and it can be refreshed when required
2. Access
- Connect remote database via odbc connectivity using link tables
- Generate report by writing a query on the linked table
- Since it is linked, it gets refreshed automatically
I want to go for ms access option since database is increasing day by day and excel will not be hold it.
But linking tables takes more time. Can you please suggest to extract data from remote database without linking tables?
It is not required to link the database in real time ie., No automatic refresh of data is required. Periodic refresh is enough.
Not the whole table is required to be linked. Query which returns current year data from the database is enough to generate the report. So, is there anyway to link query in ms access? Please let me know how to connect remote database fastly?
View 1 Replies
View Related
Feb 9, 2006
i am using access as a front end to SQL Server 2005 and link tables via ODBC.
Today when I run a query (normally works fine) I receive an ODBC Failed message box. I have tried opening each link table connected to the query and can open fine.
can anyone suggest other reasonf or this failure?
View 2 Replies
View Related
Mar 8, 2006
Hi
I'm using Microsoft Query to get data from a MS Access database into MS Excel. It seems that whenever I pull data from a UNION QUERY or SELECT QUERY based on a UNION QUERY I get an error message:
"Too few parameters...expected 8"
Can anyone offer suggestions?
Thanks
View 1 Replies
View Related
Mar 17, 2006
I am running a report that is built up of several backend queries.
I have identified the query that is causing the ODBC Failed message to appear, but cannot identify what is causing the problem.
I have tried to recreate the queries starting form the base query (which works fine).
Cannot anyone suggest anything?
Thanks
Paul
View 1 Replies
View Related
Jun 21, 2006
Hi Everybody!
I am creating an Access database that will be used by multiple users. This database will be on the network and will be the front end of an oracle database connected to it via ODBC. Do I have to set up the ODBC to oracle only on the machine that hold the database on the network or do I have to set up an ODBC on each user's machine?
Thank you for your help.
View 11 Replies
View Related
Aug 8, 2006
I am having a problem with an access database that is connecting to a sql server. When trying to run any queries it is very slow. I think the problem might be caused by the ODBC link and I was wondering if there are any alternatives to using ODBC to connect access and sql.
View 5 Replies
View Related
Oct 19, 2006
I have a user that can only set up an Access ODBC driver using one listed as:
"Driver do Microsoft Access (*.mdb)"
They used to be able to set one up with listed as:
"Microsoft Access Driver (*.mdb)"
Any ideas as what the difference is and why it may have started preventing them from using the latter?
Thanks,
View 3 Replies
View Related
Mar 16, 2007
Good Morning,
Im trying to set an ODBC connection to automate the connection process.
The current code I have is:
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
MsgBox "Opening Connection...."
Set MainConn = wrkODBC.OpenConnection("Connection1", dbDriverPrompt, True, "ODBC;DATABASE=live; DSN=live;")
which works fine in bringing up a dialog box to choose the connection.
But what do i write in the database and dsn fields to enable the automation process.
See attached picture for the log in settings. All I have to do is press 'Connect' to start talking to the ODBC.
any help would be greatly appreciated.
Justin
View 6 Replies
View Related
Mar 21, 2007
Just a quick (hopefully easy) question.
Does anyone know if it is possible to link to Filemaker Pro tables for Mac, into Access?
The Filemaker DB will hold the data, with Access displaying the forms, along with some additional tables.
Does anyone have any ideas?
View 2 Replies
View Related
Jul 10, 2007
I use dsn file as below from Access to link to SQL server via ODBC.
From Access, I use this file but list all tables including system tables. How to restrict system objecs?
[ODBC]
DRIVER=SQL Server
UID=aspfun
Trusted_Connection=Yes
DATABASE=credent
WSID=aspfun_ws
APP=Microsoft Data Access Components
SERVER=order
View 1 Replies
View Related
Aug 15, 2007
Hello...
Question, do you need one for your database and if so why? I have read what they are but I haven't found really why you would need it?
Don't know if this is the right place to post this question but wanted to get some input on this.
Thanks
R~
View 5 Replies
View Related
Feb 14, 2008
My program is an Access 2003 database that connects to SQL 2000. I have a continuous form that shows between 10 an 25 records. I have coded an update button that when clicked updates a check box on each record displayed and then updates the form. Normally this works perfectly. However, sometimes when the button is clicked nothing happens for aproximately a minute then the user gets the error ODBC - call failed and no records are uodated. I have tried resetting the view from Dynaset to Dynaset (Incosistent Updates) and extending the timeout to 300. I have also tried extending the MSAceess Page timeout setting to 5000 instead of 5 under ODBC. Nothing has helped. Does any have any other solutions? Or a way to get a more specific error. I am really just guessing that it is a time out issue.
View 3 Replies
View Related
Apr 24, 2006
Hi i would like to make a question about ODBC i have created an access database that connects via ODBC to an SQL server database, with the help of someone else i have created this code that works well for the link of 1 table that i choose to link the problem is that i want to link all tables from database and not doing it 1 by 1.
Is there a way to link all tables from database ???
The code i'm using is :
Dim strConnect As String
Dim strDatabase As String
Dim strTableName As String
strDatabase = DataBase_Now.Value
strTableName = "Tbl_A_Yles"
strConnect = "ODBC;Driver={SQL Server};Server=" & Server_Ip & ";DATABASE=" & strDatabase & ";UID=" & Server_User & ";PWD=" & Server_Pass & ";"
'Checks to see if table exists. If it does, delete it.
If TableExists(strTableName) Then DoCmd.DeleteObject acTable, strTableName
'And then use the trusty old TransferDatabase method to re-link the table declared in strTableName from the SQL Server
DoCmd.TransferDatabase acLink, "ODBC Database", strConnect, acTable, strTableName, "dbo_Tbl_A_Yles"
Thanks in advance.
View 1 Replies
View Related
Nov 27, 2006
I want a linked table in my current database. The server where the other access database is already has a ODBC connection created. How to I create a linked table to that other Database through the odbc? I know this is very unspecific, but I do not know what to do on either end. Thanks
T~
View 7 Replies
View Related
Jan 7, 2008
I wonder if anyone had any suggestions regarding how to link 4 ODBC tables so the info in each of the 4 ODBC tables are in one table. The 4 ODBC tables are linked in from a mapping system. Each table has 80% the same fields with some minor differences. i dont know if a union query would be the best answer in this case as the field in each of the ODBC's tables do not exactly match. Some have 12 fields, some have 14 fields. Any help would be appreciated?
also this would be using Access 2003 if you would like to know. thanks
View 1 Replies
View Related
Mar 18, 2008
sorry if thi9s is the wrong place but I thought I would try somewhere.
I would like to create links to table trough an odbc link.
whta I have is an estimation programme that creates 4 tables in a directory/folder each time you create a new estimate I have an odbc link that points to the given folder where the tables are being created. What I would like to do is to search this folder and link specific tables to my database which is a contract control programme. the tabels are given unique name for each estimate example names are: 0001_assembly, 0001_data, 0001_increment, 0001_system
so to sum up is it possible to create a lnk to these tables on the fly. any one got any ideas and possible codes samples for doing this.
Thanks
View 10 Replies
View Related
