Migrating Data from one Access Database table to a different Access Datbase / Table

A little background

Company has a database that must have new records added to it weekly.  The new data is provided via an access database.  The old data is in a another access database.

 What I want to do is have them upload the new access database to the ASP (not .net) server and then copy the new records over to the old data to bring it up to date.

 At this point they can upload the new database. But I am having a hard time getting that info into the old database.

 

This is what I have so far,  but I get the following error

Microsoft JET Database Engine error '80040e14'

Syntax error in FROM clause

/TB-SNA/menu/HIPreport/test.asp, line 28

1    <% 
2    'Dimension variables
3    Dim adoCon         'Holds the Database Connection Object
4    Dim rsPMReport    'Holds the recordset for the records in the database
5    Dim strSQL
6    %>
7    
8    <!--#include file="includes/adovbs.inc"-->
9    <%
10   
11   'Create an ADO connection object for the Perm Database
12   Set adoCon = Server.CreateObject("ADODB.Connection")
13   
14   'Set an active connection to the Connection object using a DSN-less connection
15   adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/SNA_HIP_REPORTS.mdb")
16   
17   'Create an ADO recordset object for the Perm Database
18   Set rsPMReport = Server.CreateObject("ADODB.Recordset")
19   
20   'Open the recordset for the Temp Database
21   'rsPMReport.Open "Ticket_Detail_All", adoCon, adOpenStatic, adLockPessimistic, adCmdTable
22   
23   Response.Write "INSERT INTO Ticket_Detail_All ([MAIN_TICKET_ID], [COMPANY_NAME], [PRIORITY], [ActCareLvl], [ActMTTR], [ActOTR], [DtCr], [DtRFC], [DtCl], [Rpt5], [Rpt30], [Chronic], [YrCl], [MtCl], [WkCl], [DayCl], [YrMtCl], [MtDayCl], [MTTRMet], [OTRMet], [ResCd7], [ResCd6], [TROUBLE_DESCRIPTION], [Measured]) SELECT [MAIN_TICKET_ID], [COMPANY_NAME], [PRIORITY], [ActCareLvl], [ActMTTR], [ActOTR], [DtCr], [DtRFC], [DtCl], [Rpt5], [Rpt30], [Chronic], [YrCl], [MtCl], [WkCl], [DayCl], [YrMtCl], [MtDayCl], [MTTRMet], [OTRMet], [ResCd7], [ResCd6], [TROUBLE_DESCRIPTION], [Measured] FROM _001_main_lastweek IN '" & Server.MapPath("files/501_Wkly_PM_Report.mdb") & "'"
24   strSQL = "INSERT INTO Ticket_Detail_All ([MAIN_TICKET_ID], [COMPANY_NAME], [PRIORITY], [ActCareLvl], [ActMTTR], [ActOTR], [DtCr], [DtRFC], [DtCl], [Rpt5], [Rpt30], [Chronic], [YrCl], [MtCl], [WkCl], [DayCl], [YrMtCl], [MtDayCl], [MTTRMet], [OTRMet], [ResCd7], [ResCd6], [TROUBLE_DESCRIPTION], [Measured]) SELECT [MAIN_TICKET_ID], [COMPANY_NAME], [PRIORITY], [ActCareLvl], [ActMTTR], [ActOTR], [DtCr], [DtRFC], [DtCl], [Rpt5], [Rpt30], [Chronic], [YrCl], [MtCl], [WkCl], [DayCl], [YrMtCl], [MtDayCl], [MTTRMet], [OTRMet], [ResCd7], [ResCd6], [TROUBLE_DESCRIPTION], [Measured] FROM _001_main_lastweek IN '" & Server.MapPath("files/501_Wkly_PM_Report.mdb") & "'"
25   
26   
27   'Open the recordset with the SQL query 
28   rsPMReport.Open strSQL, adoCon, adOpenStatic, adLockPessimistic, adCmdTable
29   
30   
31   Response.Write "Completed!"
32   
33   'Reset server objects
34   rsPMReport.Close
35   Set rsPMReport = Nothing
36   Set adoCon = Nothing
37   Set strSQL = Nothing
38   
39   
40   %> 

 

 

I know this isn't asp.net... but Im hoping someone out there can help as I have been stuck for a while now.

0
kardson
12/15/2008 7:06:31 PM
asp.net.access-datasource 4679 articles. 0 followers. Follow

7 Replies
949 Views

Similar Articles

[PageSpeed] 34

Indeed, this is ASP classic, not ASP.NET so this question doesn't really belong here..

Anyway, in Access, you can link a table from one database to another database. When linked, you could use that table like it is part of the database.

I would advice you to install Database Administrator for MS Access, this is a very powerfull classic ASP tool, with the ability to link tables

0
hans_v
12/15/2008 7:37:28 PM

 

kardson:
I know this isn't asp.net...

Hans has provided a good suggestion in using Linked Tables.  But, for future reference, here's the best place for classic asp questions: http://groups.google.co.uk/group/microsoft.public.inetserver.asp.general?hl=en

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
12/15/2008 8:22:58 PM

Thanks Mike,

 Hans did have a good suggestion, but I cannot install anything on the server that is not already present...   Pain in my butt...

 

Anyhow,  thanks for the link.  I'll go bug those guys in hopes for an answer.   Would rather use .net but im stuck with legacy asp.....

 

Thanks all

0
kardson
12/15/2008 8:37:40 PM

You don't have to 'install' anything. It''s a classic ASP application, copying the files to a subfolder in the root will do the job....

0
hans_v
12/15/2008 8:43:03 PM

 

Holly crap!  lol    That has to be the single most useful thing I have come across yet!

TYVM  Hans!

 I should just be able to link the 2 tables now, and do a copy from 1 table to the other.  As if it was the same db.

 

 

<---- Happy camper here   :)

0
kardson
12/15/2008 9:17:48 PM

Smile

And you can access the linked tables also from within your code.

 

0
hans_v
12/15/2008 9:30:31 PM

 If you have SQL Server on site, you should be able to use Sql Server Integration Services to copy the data from Access database to another.


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
12/17/2008 1:31:59 PM
Reply:

Similar Artilces:

GridView data table and MS Access table. Some time I like MS Access...
Hi: I want all the rows and coloums data showing in the GridView can be editable, just like the MS Access table on the form, you can modify and update any cell and it will be saved if your mouse leave that cell (or you click "Save" button). The reason I ask is: everyday I have about hundrands of data need to be set the date(Today's date), to be set the status (on/off), and input Receipt Num, if I using Gridview's "Edit" "Update" feature, I find there are still two many clicks, and also the selected rows changing the size and p...

accessing linked Access table from .net
We have an access database with a linked table to MYOB through ODBC. The MYOB table appears in access with a green dot indicating it's a linked table. In Access it all works fine. When we try to get at it through .net, it brings up DB_SEC_E_AUTH_FAILED(0x80040E4D) which as far as I can see indicates authentication failed. Any idea what is required to be able to use it from.Net? You might try this stepp0, put in admin for the username and leave the password blank (unless you specified other) Source=C:\Inetpub\wwwroot\Components\Databases\Db.m...

xls convert sheet into Access database table and also convert access table into xls sheet in c#
hello sir how  xls convert sheet into Access database table and also convert access table into xls sheet in c# Hi, If you're working in Microsoft Excel and Microsoft Access, you can use several Access features to maintain your Excel data with the AccessLinks add-in program. For more details, see:http://office.microsoft.com/en-us/help/HA010346371033.aspx (For Excel2002) http://office.microsoft.com/en-us/excel/HP052008521033.aspx?pid=CH010003461033  (For Excel2003 and above.) Thanks.Michael Jin.Microsoft Online Community SupportPlease remember to mark the replies as answe...

Data Access Layer : Fail to read data from two table by one Table Adapter
I have an Access Data Source containing a few tables.I want to set up a DAL for data access and I add a dataset to my projectHowever, when I added two table into a table adapter, VS can't read any data from the data source (while the connection is successful as the columns and rows are loaded)  The two tables are called bidding and itemHere is the SQL generated:SELECT      item.itemId, item.shortTitle, item.picture, item.durationHour,                     ...

Accessing 2 tables from Microsoft Access database in Webpage.aspx
Hi I'm using Microsoft Access and Microsoft Visual Studio (VB) to create a webpage.I have a database (dbWarehouse). Inside in have 2 tables( tblRead, tblStock). I need to use details read form tblRead and retrieve some details from tblStock. Eg. "Barcode" from tblRead --> Access tblStock using "Barcode" to retrieve "Name" and "Qty" How can i access the 2 tables inside the webpage using SQL statements in one webpage? Regards and Thanks!   Hey, If you don't need to display the bar codes, sounds more like an inner join: select Name, Qty from tblStock s inne...

accessing access
------=_NextPart_000_029B_01C29063.1E2098D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hi! I am trying to access an access mdb file for the first time. I get the following error: Can't connect(dbi::ODBC:pfawtbls.mdb ), no database driver specified and DBI_DSN env var not set at jfa_data.pl line 147 for the code: my $dbh = DBI->connect( "dbi::ODBC:pfawtbls.mdb", "", "", ) or die "Unable to connect: ". $DBI::errstr. "<br>"; What am I missing? Thanks, Jer...

Access access
========== Hi, Everyone ... I'm going to lock this thread for now. If you have a question about working with Access in Web Matrix, please start a new thread in this forum. Otherwise, this thread will get to be unwieldy. Thanks! ========== Updated 6/29/03 Hello, everyone. I've been working with the new version of Web Matrix (version .6, build 812, available right now!). In particular, I've been playing with support for Microsoft Access, which is one of the great new features in the new release. I wrote up some notes on what I've found to share some of my experiences and to a...

Linking Access Db tables to a different Access Db using the Internet.
I am looking for the simplest way in ms Access to link up to tables in a different Access database (using ASP or not). It works in a LAN, now I want to use the internet instead. If possible without inviting a heavy load of redundant features. Anybody willing to advice?Robert...

access data... access...
Hi, I'm trying to handle a simple structured access db in c#... but I'm a bit confused on how to do it! How can I select, insert, edit and remove records from an access database? should I use datasets? Thanks I suggest reading microsoft's Documentation on ADO.Net. OleDb is the DataProvider you want to use if you're going against an Access DB. another thing: how can I get, having an opened oledbconnection, all the tables, and the fields in those tables, from the database?...

accessing ACCESS database
Hi, I am trying to write a script that copies an information from ACCESS but it gives me the following error: Can't call method "Sql" on an undefined value at franowner.pl.txt line 4. Here's the script: use Win32::ODBC; $DSN="db1"; $db = new Win32::ODBC("db1"); $db->Sql("SELECT owner_first_name, owner_last_name FROM Owners WHERE ID=11"); if ($db->Error()) { print "Error: $db->Error"; } statement:\n$DBI:errstr\n"; ($ownerfname,$ownerlname) = $db->FetchRow(); print "Database results: first ...

accessing data from a different table
Hii got a database and in that i have two tables..say one table is called 'Table A' and the other is called 'Table B'. Table A will have a field which is going to access a data from Table B.  i'm developing a website which will have adding data, editing data and removing data for the admin. And from that i have seperate pages as textboxes with buttons to add data into the database. Basically providing like an interface for admin to make changes to the database.My confusion is that i got one page that will add data to Table A, and that will have to access data from Ta...

C# Accessing OleDb access table
Once I've successfully established access with my mdb file. How do I tell my page's table to load a cell from the mdb file? This is what my successful conntion looks like... ---------------------------- <script language="c#" runat="server"> //connects to database //and saves subsribe information void DoSubscribe(object sender, EventArgs e) { //creates database connection and inserts user into subscriber newsletter OleDbConnection connection = new OleDbConnection( @"Provid...

Table Access problem when accessing a View (!)
Is this problem familiar to anybody?... To protect 'private' data columns in a table, I am accessing them through a view. The user has (SELECT) access to the view, but not the base table. The view and the table have the same owner (dbo), access on view granted to user Table name: Customer View name: Customer_v I have a C program using Embedded SQL to access Sybase To retrieve data, I am using dynamic SQL, so it's roughly as follows (this is not the actual code/SQL): strcpy(sqlstring,"select CustId, CustName from Customer_v where CustId = 131"); exec sql prepa...

Copy all data from MSSQL tables to Access table
I want to copy all data and tables from my mssql database to a new access database. Is this possible?Cheers,Curt.Regards, Curt...

Web resources about - Migrating Data from one Access Database table to a different Access Datbase / Table - asp.net.access-datasource

Thunderclap Migrating From Twitter to Facebook
Thunderclap, a mass-messaging service that gained popularity on Twitter, has set its sights on Facebook . Twitter recently booted Thunderclap ...

Facebook Migrating Unofficial Page Fans to Official Pages at Brand Owner’s Request
Rupesh Mandal is such an avid fan of the Opera Mini web browser that he set up a Page on Facebook totally devoted to celebrating it, and then ...

YouTube - Evaluating & Migrating to Cloud Storage
Hochgeladen am 31.08.2011 When evaluating cloud storage, no matter the need, there are criteria that must be placed above the rest, including: ...

UAE is the global leader in attracting migrating professionals, study finds
... with 28 per cent, was the leading source of professionals, according to the LinkedIn study. The UAE is the global leader in attracting migrating ...

Enchanting video shows hundreds of stingrays migrating through shallow waters
A video showing hundreds of stingrays migrating through shallow waters&nbsp;has gone viral,&nbsp;months after being uploaded to Facebook.

The krilling season: Whales keep tourists migrating south
Earlybird whale watchers in Eden have been rewarded with astounding acrobatic displays in the bay.

Migrating Swans were bound for glory, says great
Migrating Swans were bound for glory, says great

Researchers say Irukandji jellyfish migrating further south along Qld coast
Researchers say climate change could be altering the migration patterns of the dangerous Irukandji jellyfish along Qld's east coast.

Apple chip supplier TSMC announces record profits, migrating to more advanced technology
... Company , iPhone , and iPad continue reading at 9to5Mac . What do you think? Discuss "Apple chip supplier TSMC announces record profits, migrating ...

More eBay Merchants Migrating to Amazon in Search of Sales Growth
EBay's once-loyal merchants are moving more of their business to Amazon, saying they get more for their money by selling merchandise via the ...

Resources last updated: 12/9/2015 3:46:22 PM