SQL Server "Linked Server" to MS Access Database
I have a very frustrating problem. I have a situation that works fine
on some computers, but not on others. I have SQL Server Express 2005
and Access 2003 loaded on 4 computers. I have set up a "Linked
Server" in SQL Server linked to my Access database. I am running
queries on t-SQL against the Access tables through the linked server.
On all of the computers, I can do select queries and they all work
great but what I need to do is run Update, Insert, Delete queries as
well. On a couple of the computers, it works fine, locally and across
the LAN, and a VPN as far as that goes. But on other computers I can
do Select queries (also across the network) but no update queries. I
get the error message "OLE DB provider "Microsoft.Jet.OLEDB.4.0" for
linked server "securedjetls" returned message "Operation must use an
updateable query." where securedjetls is the name of the linked
server. The Access database on the computer that is not working is a
copy of the database from the computer that is working and any of the
tables that I'm trying to update have primary keys, so that's not the
...move/copy database objects from SQL Server Express to SQL Server Standard??
Can anyone refer me to good 'recipes' or sources of information on this
I have Visual Studio Tools for Office, which installs SS 2005 Express
locally to my XP box, and I want to develop in SS 2005, then copy the
tables or queries or reports etc. to a SS 2005 Standard server.
Thank you, Tom
If the objects are on source code control you may just want to check out the
scripts and run them through SQLCMD.
If you want the whole database then you can either use backup/restore or
sp_detach_db/sp_attach_db, failing that you could use the import/export
wizard, If SQLExpr...Adding records to a SQL Server database from MS Access
Thanks in advance for your help.
I have just upsized an Access database to SQL Server with the hope of
using a fairly extensive Access application that was part of it. But
now, the forms will not allow the addition of new records (the >*
button is greyed out). The forms use a macro to advance the dataset to
a new record. How can I get the additions (inserts) turned back on?
...Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?
Would it be possible to migrate the MS Access 2000 to MS SQL Server
My application is using MS Access 2000 as database and as user
interface such as forms. Now, I want to migrate the backend database
from MS Access 2000 to MS SQL Server 2000. However, I want to keep the
MS Access 2000 interface. Would it be possible?
If I migrate the MS Access to SQL Server, would the queries, back-end
VBA, macro, tables and forms be affected? Do I need to change the MS
Access data type to SQL server supported data type?
Which tool I can use to do the migration? Upsizing wizard or exporting...Php and DataBase Abstraction for mySql, Sql Server, and MS Access?
Is there a good DataBase Abstraction layer that will handle the
mySql, Sql Server, and MS Access engines? I do not wish to write the
same functions with the three different PHP db functions.
I see that php_dba is listed as an extension, but I determine which
DBs are supported. However, I think that it does not support the ones
> Is there a good DataBase Abstraction layer that will handle the
> mySql, Sql Server, and MS Access engines? I do not wish to write the
> same functions with the three different PHP db functions.
> I see that php_...Copy data from access database (JET) to sql server problem
This may or may not be a labview problem, but I have a set of data which has an issue if I try to copy the data from within access (2003) to an mssql (2000 sp3?) database. I was wondering if anyone else had this problem when trying to move to a different db?
I am just doing a "Insert into sqldb values select * from accessdb" command, which works find from access to access, and from access to sql within sql, but not access to sql from within access.
Britoa, Are you performing these commands in LabVIEW or from some other DB Admin tool? I wasn't sure if you were using LV to perform these operations or not, from your last sentence when you said from within access.
The commands are using access, but I was seeing if anyone else came across such issues with labview saved data (it's a flattened cluster) The data saves and loads within labview just fine from both SQL and the accessDB, but when trying to move the data from access to sql, I get those issues. It's... strange.
Britoa, Take a look at <a href="http://forums.ni.com/ni/board/message?board.id=170&thread.id=256336" target="_blank">this post</a>, it has some links that may prove useful.
...Re: Accessing MS SQL server or ORACLE database from the HP3K
One small clarification to my previous question:
We are not trying to migrate our IMAGE data to another platform. We are
trying to provide an enhancement where data that already exist in a
database on another platform can be viewed, extracted and used to
"complete" an existing entry in the IMAGE database, thus eliminating the
need for the user to manually 're-enter' the data.
I was asked the following question today and decided to turn to y...SqlSpec
Do you wish you had comprehensive, easily navigable documentation for
your SQL Server, Oracle, Analysis Server, MySQL, and Access databases?
Are you a SQL Developer or DBA that just inherited a huge, complex
database with no supporting docs and don't know where to start
learning how it's put together?
Are you an independent contractor working with SQL Server, Oracle,
Analysis Server, MySQL, or Access? Your clients would LOVE you if you
could provide comprehensive documentation about the databases you
Are you a Project Manager who is tired of developers making changes to
databases on live servers and not updating the corresponding spec?
SqlSpec will make sense of it all and generate easy-to-read docs for
you for any database on the following platforms:
* SQL Server 2000/2005
* Oracle 9i (and above)
* MySQL 5.0 (and above)
* Analysis Server 2005
* Access 97/2000/XP/2003
SqlSpec generates documentation in two formats: HTML or CHM. HTML is
useful for publishing your documents on the web or an intranet site.
CHM is useful if you want a single, compact file containing all your
documentation about one or more databases. The CHM is indexed so it is
easily searchable for any keyword. This way you can easily determine
which objects reference the "EmployeeID" column, for example.
You can download a fully featured trial version here: http://www.elsasoft.org
...Re: Accessing MS SQL server or ORACLE database from the HP3K #3
if nothing else helps, you could use Java and JDBC with the JDBC
driver of the respective target database. A small example showing
access to an Image/SQL database can be found on the following web
page (and can be adjusted to use an MSSQL or Oracle JDBC driver
and database URL/logon fairly easily)...
http://www.editcorp.com/Personal/Lars_Appel/JavaDemo/ -> JdbcProgram
Calling Java from COBOL as a subprogram, however, is probably not
the way that I would go, if exploring this road. I would probably
let a program similar to the above run as a "gateway" in a sep...Re: Accessing MS SQL server or ORACLE database from the HP3K #3
In a message dated 7/8/03 5:23:24 AM Pacific Daylight Time,
> It's not MS SQL or Oracle but for what it's worth, we once had a Cobol
> accessing a PostgreSQL database that resided on a Linux server by using the
> libraries included with Mark Bixby's PostgreSQL port.
I for one would absolutely love to see an example of that! Sometime soon (?)
I hope to be able to finally spend some time working with PostgreSQL on MPE
and/or Linux. Having a known working example of how to access it via COBOL is
a great ...Re: Accessing MS SQL server or ORACLE database from the HP3K #6
On July 9, 2003 11:23 am, Duane Percox wrote:
> * Performance was not that great so you wouldn't want to use this
> for production, but proof of concept and for learning it is ok.
Do you have any performance numbers for it all on Linux?
* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *
...Hosting advice needed for changing Access backend databases to SQL Server Express
After a virus that was so new that it wasn't in the antivirus checklist hit=
a company where I work recently, the company that maintains the server sug=
gested that Access backends not be used to store data to minimize attack ex=
posure. The VP was delighted to see that I already had plans in place to m=
ove the backend data to SQL Server Express for greater stability and for gr=
eater security. In looking at the SQL Server 2014 requirements in:
I saw included:
"Installing SQL Server on a Domain Contr...Access MS-ACCESS database on server from client????
I m currently developing a java based application and want to know how
can i make client access database located on the server?
"HeMan_Speaks" <Lunar20092010@gmail.com> wrote in message
>I m currently developing a java based application and want to know how
> can i make client access database located on the server?
I think you're going to have to expand a little bit more in your question
here, as is not 100% quite clear what you're trying to do.
You might want to make a little bit of a distinction between MS access, the
development system that allows you to write code, build forms, and build
reports, and that of the database engine that you choose to use with MS
access. When you build an application MS access, you then choose your
database engine, that might be oracle, SQL server, or perhaps more often
leave the default jet database.
So when you say make a client access application, are you talking about a
piece of software that you plan to install and each computer? The fact that
your introducing the issue of java further complicates your question.
Perhaps your question is simply you have some java code running on a server,
and with to open a mdb file (an access database file). In this situation
you're not really using MS access at all here (you using what is called the
jet database engine to read that mdb file). For all the trouble in this type
of sce...MS Access, SQL Server, Web server
I am planning to write an application using MS Access as front-end.
User enters records (mostly 15 records or less per user per day) into an
Access database. Having done that, users click a button to transfer
these records to a web server hosting an SQL Server database (set up by
a service provider). Each record represents a job; provider will update
its status when a job is done.
At the end of day, user will open up the database to enquire the records
status, which will be retrieved from the web server (through HTTP protocol)
A service provider will probably have 2000 records/jobs per day, which
will be fetched from the web server to its PC for job scheduling purposes.
Can someone advise me as to how to go about doing this ? I have read a
little on WinInet APIs of MS Visual Studio 6, but I am still not sure;
eg how to get SQL server to send data to client PC using HTTP.
Thanks for your advice.
I can't really understand the scenario here. You are going to have to
explain this better.
1. Who are the users? What do they do? Are they mobile in that they
are not connected to the network when they are working, like traveling
2. Who are the service providers? I'm assuming that these are not
Internet Service Providers or Application Service Providers, but rather
people who work for the company and do things for customers. What kind
of jobs do they do? Do they provide their services in places where
they h...Re: <Another Pluh>Re: Accessing MS SQL server or ORACLE database from the HP3K
From: "PeteVickers" <pete@GUI-INNOVATIONS.COM>
> forgive the 2nd plug, but we have just uploaded the NewsWire review to the
> web site, or use the direct link
> "Paul H. Christidis" <firstname.lastname@example.org> wrote in message
> > Is there a way for an IMAGE/VPLUS based application to connect, extract,
> > and present to the user (based on his input) data residing on an MS SQL
> > server or an ORACLE database on a different host?
MPE/iX Enterpr...Updating SQL Server Database from Access
I'm taking over an SQL Server
database which is often updated
with data from an Access database.
Knowing little about either, this
simple task has become a challenge.
I'm told that the previous person
used to type some simple SQL
commands and this would update
the SQL server database but the
commands have been lost and the
previous person is unavailable.
The Access database has exactly
the same column names as the SQL-server database
and it is something simple
Control ID Phone Name Address1 Address2
Does anyone know the SQL commands
to do this kind of SQL server database upd...Accessing an SQL Server Database with VB
I will be designing an app with VB that will access an SQL server
How difficult is this to do in VB? Is there some built-in
compatibility between the two?
Any suggestions about a book to buy would be much appreciated. The one
I'm looking at now is Beginning SQL Server 2000 for Visual Basic
by Thearon Willis.
I've had experience with VB but none with SQL server.
<email@example.com> wrote in message
> I will be designing an app with VB that will access an SQL server
&...Importing Access databases into SQL Server
I have a situation where an application needs to import data from
number of access mdb files on a daily bases. The file names change
every day. The data import is very straight forward:
insert into sql_table select * from acess_table
There are up to 8 tables in each access file and some access files will
have less. So the process needs to figure out which tables exist in
Access mdb file and import them whole into sql staging tables.
Any recommendations are appreciated.
It probably depends where you're running the load process from - that's
not really clear (t...Running a MS-Access BE and an SQL-Server BE on same TS Server
What are the implications of running a MS-Access BE and an SQL-Server
BE on the same Terminal Server?
Assuming that the TS can handle the load are there any other issues?
Patrick Fisher wrote:
> What are the implications of running a MS-Access BE and an SQL-Server
> BE on the same Terminal Server?
> Assuming that the TS can handle the load are there any other issues?
You either serve the Access "front end" as a terminal server published
application or you just stick an Access "back end" on it in which case the
server is nothing more than a file server. Which are you talking about?
Certainly in the case of the latter the added load would be minimal. If you
plan on installing Access on the server and running Access apps from the server
then the added load would be significant as your number of users climb.
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
We are talking about a customer who currently runs 4 TSs with load
balancing running SQL Server to about 80 users, the data is held on a
5th TS, this system is running a program called scala and works at an
acceptable speed accross an ADSL VPN
Against my advice they have installed our Access97 Runtime using DFS
and BE on one of the TSs, this across the VPN is very slow when Scala
users a logged on.
They have restricted the Access Runtime to on...Copy databases from SQL 2005 server to SQL Express
Two Windows 2003 server,
one with SQL 2005 server,
another with SQL Express.
Is it possible to copy databases from SQL 2005 to SQL Express?
Thanks (Thanks@work.com) writes:
> Two Windows 2003 server,
> one with SQL 2005 server,
> another with SQL Express.
> Is it possible to copy databases from SQL 2005 to SQL Express?
Yes, but I don't think you can use the Copy Database Wizard, but you will
have to do it by hand. Which is not very difficult anyway.
On source machine backup database, and make note of the logical
filenames. (You see these with sp_helpdb).
...Accessing a MS Access database across two servers
The technical support guys at my company have set up my system so that
the server containing the MS Access database is on one server, while
the pages that should access the database are on another server.
When the pages and database are on the same server, I usually just
connect through ODBC.
The problem is that I don't know how to set up an ODBC connection to
link from one server to a database in another.
Anyone know what I am getting at and know of a possible solution, or a
link to a site which may help.
thanks in advance
"Singularity" <Brendan.Collins@Singularity.co.uk> wrote in message
> The technical support guys at my company have set up my system so that
> the server containing the MS Access database is on one server, while
> the pages that should access the database are on another server.
> When the pages and database are on the same server, I usually just
> connect through ODBC.
> The problem is that I don't know how to set up an ODBC connection to
> link from one server to a database in another.
> Anyone know what I am getting at and know of a possible solution, or a
> link to a site which may help.
I think this may be more of a network issue than an Access one. Can you see
both servers from your PC? Is there any particular reason why they've been
put on different servers? Seems a little odd to...tcl database connection for Oracle MySQL PostgreSQL Sybase "Microsoft SQL Server 2000 Desktop Engine" DB2 Interbase/Firebird SQLite Microsoft Access
There are many people that are looking for/missing the support for
their database within TCL. Somehow this was overlooked:
It has native TCL API and supports most popular databases.
One problem: It doesn't have production ready support on Windows, but
for the lucky people who use Linux/Unix, it seems to be an excellent
solution, while TDBC is being developed.
One small correction, the TCL part of the db connection will work just
fine on Windows connecting to sqlrelay running on Linux/Unix (which,
in turn, can connect to the database running on Windows :) ). What
doesn't have production ready support (according to developers) is
sqlrelay running on Windows.
...Access 2k upgrade to Access 2003 or SQL Server Express?
I'm having a system using Access 2000 as both front-end (queries,
forms, reports & temp tables for reports) & back-end (data) with
back-end running on the server. The application runs on the network
with around 15 users.
The back-end database is suffering from performance issues. There were
several corruptions since few months & one major corruption & corrupted
the MSysObjects table. Also, the system will run batch process that
generate hundreds to thousands of records in the transaction table,
which caused the transaction table being locked even though users just
trying to add new transactions even though the database has been
selected to use record-level locking. Furthermore, some queries are
very slow as they query the transaction table which has over 1.3
The company has just been covered by the corporate license (as a
company of the group) to update all PCs to Office 2003. Is upgrading to
Access 2003 help to get the system more stable/reliable? Is Access 2003
not so easy to corrupt? Can we get true record-locking or still
suffering from locking pages or even the whole table when adding
hundreds of records?
As upgrading to SQL Server is not an option at the moment (due to the
budget), I'm also looking to the SQL Server Express which is free. But
how hard is it to upgrade to SQL Server Express? There are lots of
codes using DAO recordset in the application, do I need to change all
to ADO? Is the front-end still in .MDB (database) ...Running MS Access db as a Linked Server on SQL Server
Looking for a quick and dirty on running an Access database as a linked
server in SQL Server. Basically, the majority of my stuff is in SQL
Server, but there is one lingering nightmare-of-an-Access-database no
one wants to touch.
I just want to create a linked server to use the Access db.
So far I have not found a way to connect (under Security -> Link
Servers in SQL Server), though I tried all kinds of drivers, connection
strings, etc. What do I put for Product Name, Data Source, Provider
String, (Location, Catalog)?
Thanks a bunch.
This might help; note the table about halfway...