Creating an Access database in VB.NET

I have been able to create an Access DB in VB.NET using ADOX, but have some quick questions about it.  The code I used to create the database follows the questions.

1.  I have been unable to give the database a password when creating it.  I thought I could simply add "; Password='test'" to the connection string, but this doesn't work.  How can I do it?

2.  I have found that if I have too many fields (columns) in a table, I am unable to execute SQL statements on it.  Is there any way around this restriction?

Thank you.




Sub Create_Program_Defaults(ByVal DefaultsFilePathName As String)

        ' Create the database file
        Dim cat As ADOX.Catalog
        cat = New ADOX.Catalog

        Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                                    & "Data Source=" & DefaultsFilePathName

        cat.Create(connStr)


        cat = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()


        ' Create the Program Defaults table
        Call Create_Defaults_Tables(DefaultsFilePathName)


    End Sub
vdurbalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

planoczCommented:
try this...

   Public Sub MakeADataBase(ByVal sDBName As String)
        Dim cat = New ADOX.Catalog
        cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & sAppPath & sDBName & ";password=" & sDBPass & ";" & _
                        "Jet OLEDB:Engine Type=5")   'Type=4 is for Access 95
        cat = Nothing
        MessageBox.Show("done")
    End Sub

0
planoczCommented:
Sorry could code backwords....

   Public Sub MakeADataBase(ByVal sDBName As String)
        Dim cat = New ADOX.Catalog
        sDBPass = "test"
        cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & sAppPath & sDBName & ";" & _
                        "Jet OLEDB:Engine Type=5;" & _
                                   "Jet OLEDB:Database Password=" & sDBPass)        'Type=4 is for Access 95
        cat = Nothing
        MessageBox.Show("done")
    End Sub
0
vdurbalAuthor Commented:
Hi.  I tried out your code but the database is still created without a password.  I am currently using Access 2003, could this be making a difference?  Here is the code again:

Sub Create_Program_Defaults(ByVal DefaultsFilePathName As String)

        ' Create the database file
        Dim cat As ADOX.Catalog
        cat = New ADOX.Catalog

        Dim Pwd As String = "test"
       
        cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & DefaultsFilePathName & ";" & _
                        "Jet OLEDB:Engine Type=5;" & _
                        "Jet OLEDB:Database Password=" & Pwd)


        cat = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()


        ' Create the Program Defaults table
        Call Create_Defaults_Tables(DefaultsFilePathName)


    End Sub
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

planoczCommented:
I am using Access 2002 and I do not think their is a problem between the two access.
Is there something special that you are trying to do with GC.; because you don't need it here.

I would change this (less memory baggage)...

       Dim cat As ADOX.Catalog
        cat = New ADOX.Catalog

to this...

 Dim cat = New ADOX.Catalog
0
vdurbalAuthor Commented:
Thanks a lot.  The password is working now.  Just a quick question, though.  Am I still limited in the number of fields (columns) I can work with per table?  For one table I've got about 300 and there is no easy, or logical, way to split them up - they are all simple descriptors for a feed.  In a previous program, I've tried working with just the one table but had to split it into two tables because my SQL queries wouldn't work.  Thanks again.
0
planoczCommented:
If I remember you are limited to 256 columns in Access.

I would work with sql statements or maybe even work with building two tables.
But if you are looking for special info in each table it is very rare that you need  all of the columns.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vdurbalAuthor Commented:
That's what I figured.  Thanks a lot, again, for all of your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.