.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Loading from multiple Access 2007 tables

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server
 

HI

I have a requirement that i have to load data from multiple access tables in a database with same structure into sql server table having same structure of source.

Please some one help.

Regards,

Sam




View Complete Post


More Related Resource Links

How to add a Database Connection on Sharepoint Designer 2007 to access SQL 2005 tables?

  

I've tried and looked on many sites with different solutions for which non of them resolved my issue. I've a blank Sharepoint site and want to view data from an SQL DB. It says that a Server Error occurred and that the Data Retrieval Service didn't worked out while listing the DB's.

I tried using the 3 methods... using the also the connection string in different ways... when I do it using VB, Office and Java... I can see the data from that DB... why on Sharepoint Designer it doesn't? It supposed to be more easier than other tools.

I'm really desperate. Any help is very welcome.

Thanks,

____


Access 2007, SQL 2008 and linked tables

  

I have a bunch of databases running on a SQL server 2008 instance.  They're all set to the default Latin collation, and the server is set to UK English locale.

 

I have an access database using linked tables to the SQL server.  The lcal PC is Windows 7 and also set to English UK locale.

 

However, the date fields in the SQL server tables are interpreted by Access as "text" type, not date.  When I type a date into them in standard UK english format - dd-mm-yyyy, access accepts them but the SQL server rejects them because for some reason it is wanting to store the data in the format yyyy-dd-mm.

 

I realise I'm no expert, this is probably something really simple and I'm probably being stupid, but can anyone shed any light on WTF i going on here and why, and how I fix it.  I realise I could code in access forms something to force the date into yyyy-dd-mm format and re-interpret it to English when I want to display it, but that's clunky and horrible.

 

Any ideas gratefully received.  Thanks.


Insert NULL values into Integer column in Access 2007 tables

  
hi  there:

  My source data  is below and it's in CSV format

Days waited

 NULL
 NULL
  1
  2
  300
  500
 NULL
  .......

My destination is a Number column in a 2007 access table  and the required property is set to NO

First question :

   I used a Data conversion task in DFT to convert [DT_STR] to Integer as SSIS seems to default the source as DT_STR type. However, SSIS keeps complaining that

"The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

I know that's because of those NULL values. how to solve this issue?


thanks


 Hui
--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

Urgent: Webform insert into Access (multiple tables)

  

hello all, 
i have a webform that I am sending to and Access database and i am using dreamweaver cs4. The form consists of fields that would be sent to multiple database tables. I have tried creating server behaviors to insert the record, and have gotten it to add the records to the various tables, HOWEVER the foreign keys of the tables were not linked (i am using autonumbers for the Primary keys). 

i was thinking to split the web form into 3 forms...a form for each table, as i would have to retrieve the autonumber from the database after the first form is sent.  Could someone help me achieve this?

The code that was created by dreamweaver for the first webpage is as follows

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="Connections/RequestBooking.asp" -->
<%

merging multiple tables in a single dataset to single table

  

 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 


like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....



can some1 help me please.....


Performance r/w xml and dealing with multiple access.

  

Hi,

I've working on a chat type project at the moment. I was originally using a DB but decided to move to XML which has improved speed a lot and also made the control more portable. I have two related issues that I was hoping you guys could pass comment on.

The first is dealing with multiple users either reading or trying to write to the xml file when someone else already has it open. I've had to be an Exception loop around the write process to 'queue' the writes from different users. This means that sometimes there is now an issue with speed of response.

The second issue is to do with the speed at which my code read and writes xml. Speeding it up would help my app generally and but would also help with issue one.

Here is my code. Just so you are aware, when someone makes a post it does Savemessage and then Getmessages. Getmessages also runs every 3 seconds regardless:

public List<Chat.Message> Getmessages(int numberOfMessages, int lastMsgID)
    {
        List<Chat.Message> res = new List<Chat.Message>();

        DataSet xmlDB = new DataSet("xmlMSG");        
        
        xmlDB.ReadXml(HttpContext.Current.Server.MapPath("~/xmlMSG.xml"));

        if (xmlDB.Tables.Count > 0)
        {
            // I only want a maximum of the last

SharePoint 2007 Access Error

  

Hi everybody!

I´d like to verify if somebody see this scenario:

* I create a site, using te meeting site template;

* I give Full Control access to an user;

* When this user try to upload an item in a library, in this site, it was showed the message "Acces Denied".

* If the user press "back" in the Internet Explorer tool bar and press "F5" to refresh, the file is there.

* The same problem is ocurring, when he try to create a meeting.

Somebody ever see this error!?

Thanks a lot

Douglas!


Running multiple websites in Sharepoint 2007 with AX2009 Enterprise Portal

  

I need to setup two separate webshops with public access and different layouts using AX2009 Enterprise Portal.

However I am uncertain how to implement two completely different layouts/designs because the Sharepoint Templates are located inside the 12 hive and not seperate for each website.

Can anybody give me some hints on how to do this - and if so, which requirements I have to meet (VSS 3.0 or MOSS Standard / Enterprise) in order to do it.

I am speculating whether I need to setup two separate web-servers with each own Sharepoint installation (and Business Connector Proxy)

Thanks in advance

bhlj


Administrator and Developer Guide to Code Access Security in SharePoint Server 2007

  
Explore configuration options, get best practices for managing CAS in SharePoint environments, and walk through a complex CAS scenario.

InfoPath and Access - two parent tables and one child table

  

Hi there,

I'm trying to link an InfoPath form to an Access database, I want to connect the InfoPath form to 3 tables in the Access database but InfoPath will only recognise parent-child relationships in a series (e.g. the "Company" table is the parent to the "Customer" table, which is again parent to the "Orders" table)

I need to have two parent tables and one child table, though (e.g. "Customer" is parent to "Orders", but "Inventory_Item" is also parent to "Orders"). Is there any way to establish this in InfoPath? I'm using Windows XP, InfoPath 2007 and Access 2007.

Cheers, Patrick


SharePoint 2007 Expiration Policy Not working : Access denied for the site

  

I have a SharePoint 2007 List with Expiration Enabled in Information and Policy Management. I have a datatime column ExpiryDate which i used in Expiration. On Expiration I have chose to delete the item. Through Central Admin I run the expiration by clicking "Process Expired Items now" button. I got the following error in the Windows event viewer.

Expiration Service for site http://md-rmanimaran failed.

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

EventID: 5016

Category: Information Policy Management 

Where I need to specify the login details to get rid of this error? Anybody Please help..

Thanks

R.Manimaran

 


Linked Server to access Excel 2007

  

Hi

I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.


Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
go
SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]')
Exec sp_dropserver 'AB2','droplogins'


Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges

GRANT ALTER ANY LOGIN TO sqlaccount
GRANT ALTER ANY LINKED SERVER TO sqlaccount


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE

The DisAllowAdHocProcess in

Delta loading on 400+ tables, pls help.

  
Previously, we IT team developed an ETL package to create and load tables that exist in source database(oracle) but not in target database(sql server). As it may come to your mind, in the ETL package we use ForeachLoop and Oracle's sysobject to generate 'create table..' scripts to build the tables in Sql server then use script task to load data into the tables which have different structure through bulkcopy. We did not perform delta loading. Just in a drop-create-load route. Now, a performance issue comes. It takes almost 30 hours to generate and load all these more than 400 tables. The number and data volume are increasing, so we have to think about delta loading. But how to do the delta maintenance on 400+ tables? please provide ideas.   Appreciate your help. (Oracle10, SS2005, BIDS2005) Zen

Excel 2007 Filter on multiple values

  
Hi Everyone,How do I use Excel 2007 Pivot Table's label filters/value filters to filter with multiple conditions? for example, I want to get all customers with customer name start with "G" and doesn't include "E" and with measure>100 and measure between 50-60.Regards,George

Linked Server to access Excel 2007

  
Hi I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0. Apparently the query requires the sql account to have SYSADMIN privileges. Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method. Following is my code. Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1' Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL go SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]') Exec sp_dropserver 'AB2','droplogins' Now i get the following error Error.15247-User does not have permission to perform this action If I execute the query from Query Analyzer it works fine, but fails when I execute it using Windows App and encapsulate code in Stored Proc. My Excel file, Database and Windows Application run on separate machines. i have provided the following privileges GRANT ALTER ANY LOGIN TO sqlaccount GRANT ALTER ANY LINKED SERVER TO sqlaccount EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE The DisAllowAdHocProcess in Registry has value 0 Please let me know what additional permissions should i set to get it working???

SUM col from multiple tables

  
I am trying to SUM a column from each of 3 tables, where an ID field is the same in each table and the date range is the same also It would look something like this: table1      table2      table3      table4  amt         amt         amt         t4ID  ID           ID           ID  date        date        date SELECT SUM(t1.amt),SUM(t2.amt),SUM(t3.amt) WHERE (each table).ID = @t4ID AND (each table).date BETWEEN @date1 AND @date2 the t4ID, date1 and date2 will be chosen by the user from controls and supplied to the statement via a parameter array. I have tried many variations ( with more to go) to send 1 @t4ID parameter, for example, and use that in all 3 tables (same with the dates). Any help to nudge me in the right direction would be appreciated. Thanks

Problem connecting to SQL Server 2008 R2 Express from Access 2007

  
I have a problem with SQL Server 2008 R2 Express with MS Access 2007. I am running Windows 7 with 8 gigs memory. I installed SQL Server 2008 R2 Express. Do I now need to install SP1 because I am running with Windows 7? I can open SQL Server using the Management Screen but I cannot connect to MS Access 2007 using SQL Server Express as the database. When trying to connect to SQL Server from Access 2007, I keep getting the message that SQL Server 2005 cannot be found. It will not allow me to sign onto sql server 2008 stating that it cannot be found. Do I have to now run the SQL Server 2008 R2 Express SP1? Is there an upgrade to MS Access 2007 which will allow me to use SQL Server 2008 R2 Express?
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend