Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2005
    Posts
    8

    Unanswered: HELP HELP HELP. Execute .sql file for MS Access database

    I have to automate the process of writing data to MS Access database. I have created a program that was executing the dynamically generated Insert queries in a for loop. This was taking very long to execute because of the bulk of the data and the number of for loop executions. I then created a .sql file and wrote all the Insert queries into the file and now plan to execute all the queries of this file as if I was batch executing the sql queries. Using MS Access database is my constraint and I have to use C programming language. Can someone please help me ASAP? Pleaseeeeeeeeeeee

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    WHY are you asking for C help in a VBA forum????

    Tip: You cannot batch SQL statements using the Execute method ... You can however use a recordset with a Batch lock type and then perform a batch update (each record added is "Updated" to the recordset THEN is written out to the DB once) ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2005
    Posts
    8
    Well, I thought this was MS Access forum. Anyways I'll post in the C forum too. Thanks for some reply. However, I am not very clear about the solution that you have given. I have a text file which has about 400,000 INSERT statements. Now I have to read this file from my C program to achieve the goal of executing these INSERT statements in order to populate the desired table. Can you please provide some sample code. May be that'll help. Thank you.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You might have better luck if you described how you are connecting to the database.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2005
    Posts
    8
    This is the code I am using for establisting database connection.


    SQLAllocEnv (&hEnv); // Allocate memory for ODBC Environment handle
    SQLAllocConnect (hEnv, &hDBC); // Allocate memory for the connection handle

    // Connect to the data source "test1" using userid and password.
    retcode = SQLConnect(hDBC, szDSN, SQL_NTS, szUID, SQL_NTS, szPasswd, SQL_NTS);

    if(retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
    return -999;
    }

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by amolchopra
    This is the code I am using for establisting database connection.


    SQLAllocEnv (&hEnv); // Allocate memory for ODBC Environment handle
    SQLAllocConnect (hEnv, &hDBC); // Allocate memory for the connection handle

    // Connect to the data source "test1" using userid and password.
    retcode = SQLConnect(hDBC, szDSN, SQL_NTS, szUID, SQL_NTS, szPasswd, SQL_NTS);

    if(retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
    {
    return -999;
    }
    The HARD stuff ... Direct SQL manipulation ... This is gonna be a bitch my friend ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Sql

    Is creating and running a DTS Package in SQL Server scheduling it during night-time hours an option?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2005
    Posts
    8
    Dude,

    I don't understand. What do you mean by direct SQL manipulation??

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by amolchopra
    Dude,

    I don't understand. What do you mean by direct SQL manipulation??
    Uh ... Dude. Like the library you're using is a direct sql manipulator ... Unlike say ADO ... With ADO you pass off the actual reading and writing to the driver. You are not.
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Nov 2005
    Posts
    8
    Quote Originally Posted by M Owen
    Uh ... Dude. Like the library you're using is a direct sql manipulator ... Unlike say ADO ... With ADO you pass off the actual reading and writing to the driver. You are not.
    Yah. I am using C. It is non OO.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •