Problem in copying data from SQL to mdb

Question

0.00/5 (No votes)

See more:

i have issue regarding mdb and sql i want to insert data from sql to mdb what i have done :

1. created table in mdb with sql table nd columns , type etc
2.inserting each and every time like looping recordset of sql and inserting into mdb.

the problem is its taking lot of time to insert from sql to mdb any suggestion or better solution please let me known.

What I have tried:

VB

for ( CXInt nFld = 0; nFld < (CXInt)pmatrecordset->NumRows(); nFld++ )
	{
		strInsertQuery = _T("");
		strColNames = _T(""), strColValues = _T("");
		strTemp = _T("");

		strInsertQuery = _T("INSERT INTO ");
		strInsertQuery += strTName;
		strColNames = _T("( ");

		strColNames = _T("( ");
		strColValues = _T(" VALUES (");

		for ( CXInt nColName = 0; nColName < (CXInt)pMat->NumCols(); nColName++ )
		{
			strColNames += pMat->GetColumnName(nColName);
			strColNames += _T(',');
		}

		strColNames = strColNames.TrimRight(',');
		strColNames += _T(" ) ");

		for ( CXInt nCol = 0; nCol < (CXInt)pMat->NumCols(); nCol++ )
		{
			strTemp.Format(_T("\"%s\""), str);
			strColValues += strTemp;
			strColValues += _T(',');
		}

		strColValues = strColValues.TrimRight(',');
		strColValues += _T(" ) ");

		strInsertQuery += strColNames;
		strInsertQuery += strColValues;
	//inserting to mfdb
	}

Posted 15-Jun-18 2:58am

Member 13798855

Updated 15-Jun-18 9:42am

v3

Add a Solution

Comments

CHill60 15-Jun-18 9:04am

For starters you shouldn't be creating the query by concatenating strings.
If you know the table format just use a parameterized query.
However, it looks like this is meant to be generic - in which case use StringBuilder instead of "adding" to the end of strings - strings are immutable (cannot be changed) so you are actually creating a new string each time you use +=

Member 13798855 15-Jun-18 9:11am

the probelm is iam inserting every time ..thats causing issue

CHill60 15-Jun-18 10:16am

See the comment from RichardDeeming. Or you could attach to the SQL database in the Access database and link to the tables. If you absolutely must have a copy of the table on the mdb then write a simple Access query along the lines of

Select * into Accesstable from sqltable

CHill60 15-Jun-18 9:05am

Another thought - try updating to a more up to date version of Access that uses .accdb suffix.

Richard Deeming 15-Jun-18 9:34am

You could use either the Import wizard within Access, or the Export Data wizard from SQL:
Export Sql Server 2008 database into MS Access Database - Stack Overflow[^]

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2018-06-15T03:55:00

Assuming you are using CString you have already done it right by defining the strings outside the loops and assigning empty strings instead of calling Empty() which would release the memory.

So the memory of the CString objects must be only reallocated when necessary. However, you may pre-allocate memory for all strings using Preallocate() with appropriate sizes.

Other optimisations are moving not row related parts out of the row loop, and avoiding calls to Format() and Trim():

C++

// Prepare the begin of the query string which is the same for all rows
strQuery = _T("INSERT INTO ");
strQuery += strTName;
strQuery += _T(" ( ");
for ( CXInt nCol = 0; nCol < (CXInt)pMat->NumCols(); nCol++ )
{
    if (nCol)
        strQuery += _T(',');
    strQuery += pMat->GetColumnName(nCol);
}
strQuery += _T(" ) VALUES ( ");

for ( CXInt nFld = 0; nFld < (CXInt)pmatrecordset->NumRows(); nFld++ )
{
    strColValues = "";
    for ( CXInt nCol = 0; nCol < (CXInt)pMat->NumCols(); nCol++ )
    {
        if (nCol)
            strColValues += _T(',');
        strColValues += _T('"');
        // Where is str assigned in your code?
        strColValues += str;
        strColValues += _T('"');
    }
    strColValues += _T(" ) ");
    strInsertQuery = strQuery;
    strInsertQuery += strColValues;
}

Then most of the execution time should be from the database operations. These can be optimized too by using parametrised commands as already suggested in the comments.

Finally, don't use a debug build when speed matters. Debug builds add a lot of additional code; especially to string operations.

Gerry Schmitz · Answer 2 · 2018-06-15T09:42:00

Solution 2

Why bother? MS Access can "link" to SQL Server tables (among other things).

Posted 15-Jun-18 9:42am

Gerry Schmitz