Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Inserting to SQL via Access

Posted on 2008-11-17
11
Medium Priority
?
210 Views
Last Modified: 2013-11-28
Hello Experts,
I have a seemingly simple problem:
I have an Access database table which is populated via integration with
small-parcel tracking information.  

The table has four columns: Order Type, Order Number, Tracking Number, and a Timestamp (Access gen).

What I need to do is write a Query that takes this table and inserts only the tracking numbers from the last 12 hours into a SQL Database table available to Access via ODBC.

The real issue I am having trouble with is the Timestamp filter.
0
Comment
Question by:DarkLookingGlass
  • 6
  • 5
11 Comments
 
LVL 18

Expert Comment

by:ChetOS82
ID: 22977014
What is the datatype of the timestamp?  If it is Date/Time, then you could use something like:
SELECT * FROM Table1 WHERE Timestamp >= DateAdd('h', -12,  (Date() + Time()))

Open in new window

0
 

Author Comment

by:DarkLookingGlass
ID: 22977096
Chet,

Yes, the type is Date/Time.

What would the SQL Insert look like?
0
 
LVL 18

Expert Comment

by:ChetOS82
ID: 22977160
I don't know the names of your tables or columns, but probably something like this:
INSERT INTO sqlTable (OrderType, OrderNumber, TrackingNumber, Timestamp) SELECT OrderType, OrderNumber, TrackingNumber, Timestamp FROM Table1 WHERE Timestamp >= DateAdd('h', -12,  (Date() + Time()))

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:DarkLookingGlass
ID: 22977307
Chet,

I am getting "Number of Query Values and Destination Fields are not the same" error
INSERT INTO SOP10107 (SOPTYPE, SOPNUMBE, Tracking_Number) SELECT SOPTYPE, SOPNUMBE, Tracking_Number, Timestamp FROM tblTrackingNumbers WHERE Timestamp >= DateAdd('h', -12,  (Date() + Time()))

Open in new window

0
 
LVL 18

Expert Comment

by:ChetOS82
ID: 22977319
Ok, remove the timestamp column from the SELECT statement.
0
 

Author Comment

by:DarkLookingGlass
ID: 22977324
should this be done in two queries?  One to SELCT from and another to INSERT to?
0
 

Author Comment

by:DarkLookingGlass
ID: 22977343
Chet,

oops...like this?

DLG
INSERT INTO SOP10107 ( SOPTYPE, SOPNUMBE, Tracking_Number )
SELECT SOPTYPE, SOPNUMBE, Tracking_Number
FROM tblTrackingNumbers
WHERE Timestamp>=DateAdd('h',-12,(Date()+Time()));

Open in new window

0
 
LVL 18

Accepted Solution

by:
ChetOS82 earned 2000 total points
ID: 22977348
No, it is one query.  You are asking for the timestamp in the SELECT query, but you arn't using it in the INSERT statement.

Just remove it from the list, like this:
INSERT INTO SOP10107 (SOPTYPE, SOPNUMBE, Tracking_Number) SELECT SOPTYPE, SOPNUMBE, Tracking_Number FROM tblTrackingNumbers WHERE Timestamp >= DateAdd('h', -12,  (Date() + Time()))

Open in new window

0
 

Author Comment

by:DarkLookingGlass
ID: 22977372
Chet,

I think I have it.  Let me test it in situ now.

Thanks
DLG
0
 
LVL 18

Expert Comment

by:ChetOS82
ID: 22977387
We had some cross-talk there.  Yes, your final query is correct.
0
 

Author Closing Comment

by:DarkLookingGlass
ID: 31517486
Thank you, Chet!  
Tried it on a test recordset...shows up as needed.
And thanks for teaching me timestamp query syntax!!!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question