MS Access connection problem

MS Access connection problem

• John_Cronan

   

  Hi Mike,

  I had a lot of trouble with general updatting of Access Databases in ADO.NET but one thing I've been able to is to add records and that seems to be what you need.

  These declarations are in a common module

  Imports System.Data

  Public con As OleDb.OleDbConnection = New OleDb.OleDbConnection(GetConnectionString)

  Public Adapter As OleDb.OleDbDataAdapter

  Public Dataset As New DataSet

  Imports System.Data

   

  Here is my GetConnectionString Routine

   

  Public Function GetConnectionString() As String

          ' To avoid storing the connection string in your code,  

          ' you can retrieve it from a configuration file.

          Const cKnowlegeStore As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""H:\aa\DatabasePrimitives\DatabasePrimitives\KnowledgeStore.mdb"""

         

          Return cKnowlegeStore

      End Function

   

  This works and will help you populate your database.

   

  Public Function InsertRecord(ByVal NewRow As DataRow, ByVal Table As DataTable) As Boolean

          'works

          NewRow(0) = Table.Rows.Count ' Autoincrement

   

          Table.Rows.InsertAt(NewRow, Table.Rows.Count)

          Using CmdBuilder As New OleDbCommandBuilder(Adapter)

              Try

                  With Adapter

                      .SelectCommand = New OleDbCommand("Select * from [" & Table.TableName & "]", con)

                      .InsertCommand = CmdBuilder.GetInsertCommand

                      .UpdateCommand = CmdBuilder.GetUpdateCommand

                      .DeleteCommand = CmdBuilder.GetDeleteCommand

                  End With

                  con.Open()

                  Adapter.Update(Table)

                  con.Close()

                  Dataset.AcceptChanges()

                  Return True

              Catch e As Exception

                  MsgBox(e.Message, MsgBoxStyle.Exclamation, "ADONET.InsertRecord")

                  Return False

              End Try

          End Using

      End Function

   

  Here's what to when you need a row....

   

  Public Function GetNewRecord(ByVal Table As DataTable) As DataRow< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

          GetNewRecord = Nothing

          Try

              Return Dataset.Tables(Table.TableName).NewRow()

          Catch e As Exception

              MsgBox(e.Message, MsgBoxStyle.Exclamation, "ADONET.GetNewRecord")

          End Try

      End Function

   

  Honest to goodness, I’ve had trouble with other updates, but this does work.

   

  Good luck!

   

  
  
  

• csexpert

  ok but when I change the data source to MDB the only thing I can do is type in as "connection string" and I could not find a good one to work !!

  Mike

  
  

• salvatore

  hi,

  i guess jmcilhinney answered the question but i just want to add one point

  jmcilhinney wrote:

  Here are the steps that i took: Select 'Add new Data Source'. Select 'Database'. Press the 'New Connection' button. Change the Data source to 'Microsoft Access Database File'. Press the 'Browse' button to navigate to an MDB file.

  6) when the wizard ask you to import the database to your project hit no

  or other wise you can write your connection

  Dim dbconn as oledbconnection
  Dim dbcomm as oledbcommand
  Dim dbreader as oledbdatareader
  Dim sqlstr as string

  dbconn = new oledbconnection("provider=microsoft.jet.oledb.4.0;data source=full path to your database")
  dbconn.open()
  sqlstr = "select * from products order by itemnumber"
  dbcomm = new oledbcommand(sqlstr, dbconn)
  dbreader = dbcomm.executereader()

  While DBreader.Read()
  'do some stuff

  End while

  dbreader.close()
  dbconn.close()

  hope this helps

  
  
  

• rdmorgan

  Here are the steps that i took:

  1. Select 'Add new Data Source'.
  2. Select 'Database'.
  3. Press the 'New Connection' button.
  4. Change the Data source to 'Microsoft Access Database File'.
  5. Press the 'Browse' button to navigate to an MDB file.

  
  

• dozaone

  Well... I have a diferent problem.

  I connected the Database using the Wizard, remember that it creates:

  -DataSet

  When you add a DataGridViewer other elements are created:

  -BindingSource

  -DataAdapter

  I did charge in the DataAdapter the contain from a Text File and when I use the .Fill command the Information loaded appears but I don’t know how to transfer the information into the physical database (Access of course).

  Do you have any idea

  
  
  

• glw

  CapitanMiki wrote:

  It seems to me impossible to do a very simple thing as to connect to a MDB or to a TXT file as a record source. From the Data Source Configuration Wizard I clicked on New Connection, changed the data source to MS Access and... zac ! I can only input a connection string, no possibility to point at the file in any assisted way.

  Just an FYI, you can't create a data source connection to a TXT file using the Data Source Configuration Wizard. You can only use code. There is an example in the below newsgroup link:

  http://tinyurl.com/9c8eg

  Otherwise the steps posted by jmcilhinney for an Access database should work for you.

  
  
  

• jooolaaab

  All I'm saying is that those are the steps that I took to add a data source visually, which is what the poster wanted. That may or may not be the way that I'd choose to do it when I get around to using 2005 in anger, but it does answer the question. Wizards aren't necessarily my favourite way to get things done either but using one doesn't mean that you can't still understand what's going on underneath.
  
  

• David Winningham

  Ieeeeeeeeeeeee

  J,

  That's the first thing I've ever seen you say that I disagree with, not because it's wrong but because it removes us from code. I really don't like drawing a database or the clicking because I don't know what the code is doing.

  I will choose to initialize my own datastructures etc, just so I can understand what's occuring.

  
  
  
• MS Access connection problem