Storing array values into an Access database table from a program

10 pts.
Tags:
Arrays
Database
Microsoft Access
SQL
Visual Studio 2005
Hi, I’m trying to write a program that stores the values in an array into a database table. The name of the database is Company.mdb and the table that I want to store values is called Employee. Below is the procedure that is supposed to perform the task. The problem is it doesn’t store the array(strEmployeeRecord) values into the table. Nothing happens. Can someone please help me with this problem? I’m using Visual Studio 2005. Thanks in advance.
Private Sub StoreInTable()

 Dim strSQL As String = "INSERT INTO Employee FROM ARRAY strEmployeeRecord " 

'strPath provides the database type and path of the Company database
        

Dim strPath As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source = D:Company.mdb"

 Dim odaRoute As New OleDb.OleDbDataAdapter(strSQL, strPath)

 odaRoute.Dispose()
 End Sub


Software/Hardware used:
MA Visual Studio 2005

Answer Wiki

Thanks. We'll let you know when a new response is added.

First, this syntax:

<pre>INSERT INTO <table name> FROM ARRAY <array name></pre>

Is not standard SQL, and it would only work (I think) on Visual Fox, and I guess this is Visual Basic (you didn’t mention the language being used).

Second, to use this constructor of the OleDbDataAdapter class you have to use a SELECT statement. An insert statement can be specified for the InsertCommand property, but it won’t be executed when instantiating the object.

-CarlosDL

—————————

First thing I noticed was that the target database pointer had no slash after the D: in it. Indicating a full path, not an offset of the currently active directory on the D; drive.

This could be because the slash gets lost when you enter it into this forum text box.

Reason I mention it is because, you could end up with a database copied to the subdirectory of the active directory on the D drive, or the routine will not work because it can not find the table pointed to and will throw up an error. Do you catch any errors produced? (You never mentioned error reports)

Is nothing happening because the table has not been opened?

I guess you open the table at the beginning of the whole application. So, does it remain open, or does another sub close it before this sub can write to it?

as a starting point, I’d suggest opening the table, do the write and then close the table all in the same sub.

———–

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Chippy, tables don't need to be 'opened'. You open connections to the database, not tables.
    69,590 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following