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

Amarbir Singh 21 Reputation points
2021-03-12T13:55:14.393+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,315 questions
{count} votes

1 additional answer

Sort by: Most helpful
  1. CarrinWu-MSFT 6,866 Reputation points
    2021-03-15T06:40:34.05+00:00

    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:

    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.

    1 person found this answer helpful.