I have create an Excel application which connects to an SQL Server backend.I have written an Excel vba procedure to update a table via ADO.But it couldn't---the error message says the provider (sqloledb) does not support updating database.
View 2 RepliesI have a requirement of generating an "Sales Report" excel file and mailing that file to a particular email id. I have generated the excel file through bcp command and mail is working fine.
However formatting of excel file is required by end user for eg. Cells to be merged and borders to be given for cells for which i think bcp command will not work.
Hello, I am a relatively new user to SQL so forgive me if the solution is not very dificult :) I have a SQL database that contains a products table that includes part numbers with part descriptions. I need to change all the part descriptions (over 6000 records) so I exported the table into Excel. All the changes have been done in Excel but i am having problems trying to re-import the new data into the existing table. Any info would be appreciated. Thanks
View 4 Replies View RelatedHi, I m trying to update a table using an Excel file using the following code:
[sql]
UPDATE cargo
SET cargo.slot = (SELECT two
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:excel est.xls";Extended properties=Excel 5.0')...Sheet1$
WHERE cargo.container = one)
WHERE EXISTS (SELECT two
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:excel est.xls";Extended properties=Excel 5.0')...Sheet1$
WHERE cargo.container = one)
select container,slot from cargo
[sql]
Now, I have two similar databases, and both contain all the fields necessary for this operation and these fields are identical.
However on one table it works perfectly, on the other it gives an error:
MSDTC on server 'SCRBSQLITGTO002' is unavailable.
The server I am running is the only sql 2000 server we have, so it is cannot be in cluster mode.
Can anyone help?
Thanks
Azh
Is there a way to update a specific cell(s) in an excel? I have an excel with charts and graphs which use as data source a range of cells from another sheet within the same spreadsheet. Is there a way update a specific cell from within the sql using openrowset()... ?
thanks
I am trying to update a SQL table using an excel file which has 2 columns FMStyle and FMHSNum.
FMStyle is my link to the SQL table.
Here is what I have for code....
--------------------------------------------------
Update DataTEST.dbo.zzxstylr
SET hs_num = (select FMHSNum FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c: empStyleHSCodesLoad.xls;HDR=YES ', [Sheet1$]))
Where FMStyle = zzxstylr.style
--------------------------------------------------
Everything seems to be ok except for the "Where FMStyle" is giving me a message Invalid Column name on FMStyle. Do I need to qualify FMStyle and if so how.
MSSQL noob here. Sorry guys, but I really need some help.
I have a MSSQL (2000) database and a Products Table. Now, there was three columns used for pricing that I was not able to populate on the initial usage. So I exported the table as an Excel document, and got rid of everything except the UID (unique identifier column) the Name, and the Three columns, so that the customer could take their time in filling out the information.
Ok, so These are what are common to both the MSSQL database and the Excel sheet
column uid = unique id integer
column Name = text
column eBay = number
column PriceGrabber = number
column Amazon = number
So, any idea how I can update my Products Table with this?
Please HELP!
I have an sql server table which serves as a criteria table for my sql server query.
i wish to update the sql server table from the excel worksheet. The intention is to allow the end user to change the values in a specific column in the sql server table via excel.
The table in question has the following fields
SELECT
[Cluster]
,[Max_Break_btw]
,[RefD_Max_Break]
,[DischD_Max_Break]
,[MaxReviewPeriods]
FROM [databseName].[dbo].[SpellClusterAssum]
I will like to change / update the values in the "[Max_Break_btw]" column.
Hi there SQL TEAM
Can someone please point me in the right direction?
We have a customer who would like us to write an application in flash that has a SQL 2005 Backend
Can Flash integrate with Microsoft sql 2005?
And if so are there any tutorials?
I created a SQl Server 7 database and used an Access 97 frontend on the same computer. The Access frontend uses linked tables to reach the SQl server. The security setup I have is based on the NT Domain Accounts and whatever group
I have them in (example: Mr. D is in Marketing therefore he is in a NT Domain
Group called "SQL_MARKETING"...the SQL Server recognizes that everyone in this
group has access to whatever role I have designated for it in the database").
I can access the linked tables fine on my computer at my desk or at the server. Another person with the exact same administration rights as me can
get to the server through the network neigborhood or by mapping..and they
can access the Access frontend with no problem from their computer. But
when they try to open a linked table they recieve an error message that
the ODBC connection has failed. We are all running Access 97 service pack
2 and another person and me have the exact same administration..but only
I am able to access the Access linked SQL tables but not him.I tried deleting
the ODBC system dsn on the server and the linked tables and then re-adding them and the ODBC link but to no avail. Does anyone know why this would
be occuring? Thank You in advance.
and the
We are entertaining the idea of moving our data to a cloud and I need to know how to convert my Access backend DB to SQL. which version of SQL will I need to use to do the conversion and will also work in a cloud environment.
View 2 Replies View RelatedHi,
I have some knowledge of SSIS and CRM. But i don't know how to create Integration Service using CRM 3.0 as my external data source. If anybody know about this, pls give steps to create Integration Service as soon as possible.
Thanx in advance.
I have an exe file which was generated using a visualbasic application and has a backend sqlserver database. the exe works fine on my computer which is also connected to sqlserver and the same exe when i run on the clients computer does not work and gives an error ...related to converstion of datetime datatype ...
View 2 Replies View RelatedI have a database with a sql backend and an access frontend. I put it in a folder on the network for users to access. First, they were getting errors like not being able to access the database if someone else had it open and not being able to perform certain functionality. It was suggested that i put a mde on each of the users pc. i gave them all access to their own front ends on the folder because they use different pcs. this solved the problem. however, i need a way to perform updates without manually creating mdes for every user. i used to work for a co that there was one mde was put into a folder on the network with a config file. The users access the database through CITRIX. Whenever they accessed the shortcut on the server, it created the users their own front end. Whenever we need to provide updates, we simply made a new mde from the mdb and whenever the user accessed the database again, they would have the latest mde and it would created an update frontend for the user. Does anyone know of such an utility or know what I am referring too? I encounted several suggestions like a batch file; but they refer to putting the file and the mde on each users pc. This is not how i'm set up. Thanks
I add a table to sql studio express, but i cand see it in access backend in the other computer.
What is wrong?
Thanks,
Azi
My question is this: "Does anyone know where to set up the connectivity for an access database to store data on an Sql Server?"
View 6 Replies View RelatedHi,
Currently I am developing a project which requires to generate time-consuming reports in ad-hoc real time basis. The mechanism is
(1) User submits report request with required parameters
(2) The request will be processed one by one (I guess a queue is required to monitor the number of requests and the status of each request)
(3) Reporting service will get the report request and generate report in real time.
(4) Once report is generated, send the report to user
So, is there any idea on how to handle this type of mechanism in reporting services? Any tool in MS reporting services that can help?
Thanks in advanced.
If I'm using an Access front-end, and the data is on SQL Server being accesses via a linked table, and I create a query in Access, Where is all the work done?
I know access has the option of using a pass-through method, but if I do not use it, is Access processing the query locally? I plan on migrating several tables to SQL because the sizes are getting to large for Access and want to know if their will be a performance increase with out re-writing the queries in Access.
Thanks in advance,
Adam
Hello. I have an Access 97 frontend and a SQL 7 backend. When I try to open up the linked SQL tables in the frontend I recieve the error " This recordset is not updatable" from Access. I have the permissions in SQL set at this point to public having all permissions but it still does not change my problem. Does anyone know the answer?
View 3 Replies View Relatedhow can i do an access frontend to sql server backend
View 4 Replies View RelatedWhenever we run the mdx query in SSMS, how it will fetch the data from cube.
Using XMLA script used to fetch the data or any thing else ..
hello,
is it possible to use reporting services 2005 with an oracle warehouse?
if so, how does work?
can someone advise me as to what i have to do to connect an access frontend to an sql server express backend db on another server.
can i somehow link the tables so a user can enter in data in the access db they are familiar with but it will actually go into sql server.
please help
Hi, can anyone post the steps to upgrade the backend of issuetracker to sqlexpress 2005? Thanks a lot.
View 1 Replies View RelatedThis maybe belongs in the Data-Access Forum, but I'm not sure.
Is it generally a better idea to enforce things like unique constraints in the Database or the Webform? Say for example I want to make sure no duplicate Social Security Numbers are entered. Is it better to have an "If Exists" clause in my query, with a function to deal with it in the application or is it better to just fire the data to SQL Server and let the unique constraint on the dbase column deal with it? I then still have to have some code in my application to deal with the potential exisatance of that number, so is it a case of tomatoe, tomahtoe? If I understand things correctly, SQL server will return an error code if the piece of data does exist, and I will be able to parse the error code and display a message to the user.
Are there performance/coding issues involved? Best practices?
Ok, little frustrated hence the excessive exclamation points.
I'm designing a database in access to use a sql backend. Table structures are setup and am creating a view to join multiple tables together for data entry purposes. Tables are joined properly but when I add more than one join, ie adding more than two tables, I can't add records!?!
I checked the joins, it's not the table, since if I add one table its ok, remove table, add another table and that works, but when I add both tables together, it prevents me from adding records!
And another thing, when I have just two tables joined together and add a record, it comes up with this save changes, drop changes or cancel thingie!!! WTF!!!?
Is there something simple i'm missing or is it my table structure????
Thanks in advance!
Hi, this is my first post (hopefully of many) on this board. Just wanted to say a quick hello before I dive into my question. :)
As the title suggested, I have to develop a MS Access form app (yes, it has to be Access - I know it sucks) that will post and query data to and from a remote SQL Server db. While I have no problem linking the two through the default ODBC drivers, my question is security. Some (actually most all) of the data being passed back and forth is sensative information, and I would like to know the best way to keep it safe.
If anybody has any suggestions, instructions, or can redirect me to a good FAQ site on the subject, I would be most appreciative. I have already done a search on these forums for an answer, but have sadly come up short.
Thanks to all in advance!
My company currently has about 20-25 Ms Access Database that they want toreplace the FE with .net and the BE on SQL.This will be done using Visual Studio 2005. Once the FE is converted to .netand the BE is SQL they all will be accessed through our intranet (sharepoint).I work in Ms Access and intermediate at VBA and just learing SQL through theENTERPRISE MANAGER SCREEN.I am just now looking at what Visual Studio 2005 is, but can some one tell mehow this will all connect?What is the typical route for this process?Ms access to SQL - upsizing wizard or SQL importing?????Ms Access FE to .net - summarize how this is done in visual studio (user facerebuilt) then placed on sharepoint?Can anyone sum this up?--Message posted via http://www.sqlmonster.com
View 4 Replies View RelatedHi,I'm new to SQL and databases in gnereal but am trying to source anapplication for windows that will sit on top of a small SQL database Ihave (created for local scout troup), I want an easy way to retrieve/add/view data from backend in nice menu-driven GUI format (no cmd-line).Can anyone recommend a useful app I could source to do thistask....thanks in advanceterry
View 3 Replies View RelatedHi!
I am evaluating an architecture for one of our project... a SQL database containing the data (backend) and a second database containing the development code (frontend) linked to the backend with synonyms.
It enables to upgrade the code without touching the data. Or to change the backend / use a different set of data at will.
Everything was going fine, the behavior was expected to be EXACTLY the same with synonyms as with real tables. But I came accross a problem:
Let's say we have a synonym (frontend) dbo.TABLE1 that points to a table (backend) with a IDENTITY column.
I have a sp (frontend) with the following code:
INSERT INTO dbo.TABLE1...
SELECT @SCOPE_IDENTITY = SCOPE_IDENTITY()
Well in that case, @SCOPE_IDENTITY is NULL!
Anyone has ever faced that problem? Should I use another function to return the last ID inserted? Or is it the backend/frontend architecture that is completely flawed? I also heard there's a way, by creating the tables and the code on different filegroups, to restore only the tables or the code...
Thanks for your input!
Frantz
Hi,
We are new Access users and trying to take our database live...here are some of the problems we're facing.
When we split our Access database into a Backend and Frontends, a couple of strange things happened in the Frontend:
1.) When we run our queries, multiple copies of the same record are displayed (this is wrong).
2.) When we look at those queries in design view, the field names are preceded by Expr1, Expr2, Expr3, etc.
We have a large number of queries. How can we fix this problem?
Also, if anyone knows any websites or resources that can help us understand what things like Expr1 mean (a reference) that would be helpful too!
Thank You!!!
Hi,
We are not a very advanced group of access users and are trying to split our database for a research study...here is our question:
When we split our Access database into a Backend and Frontends, a couple of strange things happened in the Frontend:
1.) When we run our queries, multiple copies of the same record are displayed (this is wrong).
2.) When we look at those queries in design view, the field names are preceded by Expr1, Expr2, Expr3, etc.
We have a large number of queries. How can we fix this problem?
Also if anyone has a good source for trouble shooting this or simmilar difficulties we'd appriciate any spots or advice.
Thanks!
I'm having a great deal of difficultyconnecting to SQL 2005 sp1 during the running of the deployment tool on the FE.. The error message indicates that I have to install the Backward compatible tool on the FE... Did that and upgraded SQL to SP1... Still a no-workie..Exactly what SQLs are compatible with the Public version of OCS... It worked fine with the Private version and SQL2005...
View 2 Replies View Related