?
Solved

Creating an Access database in VB.NET

Posted on 2005-03-02
7
Medium Priority
?
165 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:vdurbal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 13443862
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
 
LVL 27

Expert Comment

by:planocz
ID: 13443933
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
 

Author Comment

by:vdurbal
ID: 13444163
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Expert Comment

by:planocz
ID: 13448606
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
 

Author Comment

by:vdurbal
ID: 13452753
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
 
LVL 27

Accepted Solution

by:
planocz earned 1000 total points
ID: 13452921
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
 

Author Comment

by:vdurbal
ID: 13452961
That's what I figured.  Thanks a lot, again, for all of your help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question