question

AmarbirSingh-4671 avatar image
0 Votes"
AmarbirSingh-4671 asked AmarbirSingh-4671 commented

Code for System to Automatically Import data from MS Access Database into Sql Server Db?

I have tried to import the data by SSMS > right-click Database > tasks > Import data > and so on. And it imports the required tables once and then I query to insert the source table into the destination one. like:

 *INSERT INTO [ASW-Core-Shop].[dbo].[CasterHeats] ([HeatIndex], [CastSize], [Instructions])
 SELECT  SRC.HeatNo, SRC.CastSize, NULL
 FROM tblCastSetup AS SRC 
 WHERE dbo.udf_HeatIndex(SRC.HeatNo) NOT IN (
     SELECT HeatIndex FROM CasterHeats)*

But the problem is: Every now or then, new record gets created in MS Access database. So, I want this process to automate itself. I am using VB.NET in .Net Framework (windows form) and SQL connection con.

I have already connected the DataSource option in Visual Studio to MS Access db file but I do not know how to query the table name and its data from a DataTable.
I have tried like this but in vain:

 *"INSERT INTO [ASW-Core-Shop].[dbo].[CasterHeats] ([HeatIndex], [CastSize], [Instructions])
     SELECT  SRC.HeatNo, SRC.CastSize, NULL
     FROM **" & ASWCasterHeatsDataSet.tblCastLogDataTable & "** AS SRC 
     WHERE dbo.udf_HeatIndex(SRC.HeatNo) NOT IN (
         SELECT HeatIndex FROM CasterHeats)"*

All in All, I want the system (VB.NET) to automatically run this SQL query without any error.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AmarbirSingh-4671, We have not get a reply from you. Did the answers could help you? If there has an answer helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

The solution to your problem is to convert your back end to SQL Server and use Access as a front end only.

See:

https://support.microsoft.com/en-us/office/migrate-an-access-database-to-sql-server-7bac0438-498a-4f53-b17b-cc22fc42c979

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
1 Vote"
CarrinWu-MSFT answered AmarbirSingh-4671 commented

Hi @AmarbirSingh-4671,

I suggest that you can use SSMA to complete the migration, and Microsoft has provide a list of documents about how to migrate data from Access to SQL Server. Please refer to SQL Server Migration Assistant for Access (AccessToSQL) to get the details.

For the STEPS, please refer to Migrate Access to SQL Server. There have three parts which including Pre-migration, Migration and Post-migration, you can follow the steps to complete the migration, see below:

77599-ssma.png


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



ssma.png (108.3 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Carrin-msft,

Thank you for your great answer, but I have a question in my mind - After following the steps listed in the website, will it automatically keep picking up new data since the last import/migrate process OR do I need to import data repeatedly by following the same procedure?

0 Votes 0 ·

The "migration" will move the data to SQL Server. No data will remain in Access.

0 Votes 0 ·

In that case, I might not try to "migrate" because the current system (Access) will fail, I doubt, if there were no data in them. I want the new system (SQL Db) to automatically pickup the new data everyday. And, I can't make any big change in MS Access as it was written in 2011 by some unknown programmer and now rewriting it to make the back-end as SQL can't be that much efficient, I guess (Correct me if I am wrong).

0 Votes 0 ·
Show more comments