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

Question

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.

Answer 1

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

Answer 2

Hi @Amarbir Singh ,

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:

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.