CODEWITHSUNDEEP
arraysvb.netms-accessforeach
gromit1
gromit1

Reputation: 587

How to Loop Through an Array and Create Tables in a Microsoft Access Database?

I have an array named tickerArray with five ticker symbols in it. For each ticker symbol in tickerArray I need to create a table in my Microsoft Access Database. I need each table to be named for its respective ticker symbol. I know how to create the tables through vb.net but I run into trouble when it comes to naming each table. As you can see in my code below I try something like For Each tickerValue in tickerArray and then I try and use tickerValue to name each table respectively. But the code below creates the first table and names it "tickerValue" and then when it creates the second table it throws and error stating that there is already a table in the database named "tickerValue". Any ideas on how to name each table after each ticker symbol? Thanks in advance!

Imports System.Data.OleDb

Public Class Form1

Public Shared tickerArray() As String = {"GOOG", "V", "AAPL", "BBBY", "AMZN"}

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    For Each tickerValue In tickerArray
        'connection string
        Dim my_connection As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database\customers.mdb"

        'query string
        Dim my_query As String = "CREATE TABLE tickerValue ( Customer_Number Counter)"

        'create a connection
        Dim my_dbConnection As New OleDbConnection(my_connection)

        'create a command
        Dim my_Command As New OleDbCommand(my_query, my_dbConnection)

        'connection open
        my_dbConnection.Open()

        'command execute
        my_Command.ExecuteNonQuery()

        'close connection
        my_dbConnection.Close()
    Next
End Sub

Upvotes: 0

Views: 812

Answers (1)

Derek Tomes
Derek Tomes

Reputation: 4007

You should probably listen to everyone who is telling you this is a bad idea.

But your problem is a bug caused by inserting the name of the variable into you SQL rather than the value held in the variable:

Change this:

'query string
Dim my_query As String = "CREATE TABLE tickerValue ( Customer_Number Counter)"

To this:

'query string
Dim my_query As String = String.Format("CREATE TABLE [{0}] ( Customer_Number Counter)", tickerValue)

Upvotes: 1

Related Questions