none
Updating a database using LINQ RRS feed

  • Question

  • Hello,

    I need to know if it is possible to update a database using LINQ?

    Regards,

    JohnDBCTX


    jp

    Saturday, March 10, 2018 11:31 AM

Answers

  • Which would be simpler? Would it be using a query builder instead?  They use the Fill By method often.

    Maybe using the Query Builder within the VB.net IDE would be easier for me since I may have known so much about statements from Access SQL.  I use Microsoft Access a lot.

    Regards,

    JohnDBCTX


    jp

    Yes there are easier methods but that does not mean that they are equal or better than using Entity Framework. Using the query builder in Visual Studio is appealing because it is easy to use yet down the road many find this method troublesome. It's old and only good to dip your feet into working with data.

    You are much better off (if Entity Framework is too much for you at this time) is using SqlClient or OleDb data providers dependent on the backend database. If this route seem right for you then first learn how to write SQL as in my MSDN code sample. Next up, plan out your project. As you are writing code at the same time write unit test, see my primary code sample/article.

    Dependent on your data flow, learn to work not just with clean data but bad data as described in my code sample.

    Here is a code sample I did as a primer on working with data in general done in ms-access but the patterns shown are for any database e.g. ms-access, sql-server etc.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by JohnDBCTX Saturday, March 10, 2018 1:01 PM
    Saturday, March 10, 2018 12:48 PM
    Moderator
  • In addition, the next screenshot demonstrates whether I would use the Insert method.

    I do hope now I may have concluded that using the Query Builder dialog would be a waste of time.

    Whereas, I would use the Insert method, which its senses three parameters to fill in.

    Regards,

    JohnDBCTX 


    jp

    Yes, TableAdapter method is a waste of time as mentioned in my first reply :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by JohnDBCTX Saturday, March 10, 2018 2:41 PM
    Saturday, March 10, 2018 2:36 PM
    Moderator
  • SQL coders need to conduct more research before they can bridge that paradox issue.

    If there is more than one method on how to perform this in SQL, then it should be done with the keywords,

    PARAMETERS, and VALUES following the named parameters as I have mentioned in the screenshots.

    Regards,

    JohnDBCTX


    jp

    • Marked as answer by JohnDBCTX Saturday, March 10, 2018 2:45 PM
    Saturday, March 10, 2018 2:45 PM

All replies

  • I'm not a Database person but please see How to: Modify Data in a Database by Using LINQ (Visual Basic) and the other links above and below it on the left side of the page.

    La vida loca

    Saturday, March 10, 2018 11:40 AM
  • Hello,

    What Mr MonkeyBoy pointed to is perfectly valid yet (even though the last edit for the docs was in 2015) not the preferred method, instead Entity Framework is. 

    First off I don't have sample code in vb.net, only C# found in my MSDN code sample. Things that Entity Framework is better at the LINQ to SQL

    For a delete operation, you can mark a record for deleting then un-mark it if needed. The following is a translation from my C# code sample

    Public Sub Remove(ByVal sender As Customer)
    	Using context As New NorthWindEntities()
    		context.Entry(sender).State = EntityState.Deleted
    		context.SaveChanges()
    	End Using
    End Sub

    For an update we can setup validation easily along with undue pending changes

    Public Function UpdateCustomer(ByVal EditedCustomer As Customer) As Boolean
    	Using context As New NorthWindEntities()
    		Try
    			context.Entry(EditedCustomer).State = EntityState.Modified
    			context.SaveChanges()
    			ValidationMessage = ""
    			Return True
    		Catch ef As System.Data.Entity.Validation.DbEntityValidationException
    			UndoPendingChanges(context)
    			ValidationMessage = ef.DbEntityValidationExceptionToString()
    			Return False
    		Catch e1 As Exception
    			UndoPendingChanges(context)
    			ValidationMessage = "Update failed"
    			Return False
    		End Try
    	End Using
    End Function

    Form code for add/edit

    Imports System
    Imports System.Collections.Generic
    Imports System.Windows.Forms
    Imports EntityFrameWorkNorthWind_cs
    
    Namespace Example1_cs
    	Partial Public Class EditorForm
    		Inherits Form
    
    		Public Property Customer() As Customer
    		Public Property Adding() As Boolean
    		Public Sub New()
    			InitializeComponent()
    		End Sub
    		Public Sub New(ByVal customer As Customer, ByVal contactTitles As List(Of String))
    			InitializeComponent()
    
    			Me.Customer = customer
    			cboTitles.DataSource = contactTitles
    			cboCountry.DataSource = Countries.Names
    
    			If String.IsNullOrWhiteSpace(customer.CompanyName) Then
    				cboTitles.SelectedIndex = -1
    				cboCountry.SelectedIndex = -1
    				Adding = True
    				Me.Text = "Adding"
    			Else
    
    				Adding = False
    
    				Me.Text = "Editing"
    
    				txtCompanyName.Text = Me.Customer.CompanyName
    				txtContactName.Text = Me.Customer.ContactName
    				txtAddress.Text = Me.Customer.Address
    				txtCity.Text = Me.Customer.City
    				txtPostalCode.Text = Me.Customer.PostalCode
    
    				Dim index As Integer = cboTitles.FindString(Me.Customer.ContactTitle)
    				If index <> -1 Then
    					cboTitles.SelectedIndex = index
    				End If
    
    				index = cboCountry.FindStringExact(Me.Customer.Country)
    				If index <> -1 Then
    					cboCountry.SelectedIndex = index
    				End If
    			End If
    		End Sub
    		''' <summary>
    		''' Validation is done in the main form.
    		''' </summary>
    		''' <param name="sender"></param>
    		''' <param name="e"></param>
    		''' <remarks>
    		''' * No reason not to place validation here, I left that for you :-)
    		''' * I avoided checking if the text properties of the TextBoxes 
    		'''   are empty as we are focused on EF validation not form control 
    		'''   validation
    		''' </remarks>
    		Private Sub cmdSave_Click(ByVal sender As Object, ByVal e As EventArgs)
    			Dim customers As New Customers()
    
    			If Adding Then
    				Customer.CompanyName = txtCompanyName.Text
    				Customer.ContactName = txtContactName.Text
    				Customer.ContactTitle = cboTitles.Text
    				Customer.Address = txtAddress.Text
    				Customer.Country = cboCountry.Text
    				Customer.City = txtCity.Text
    				Customer.PostalCode = txtPostalCode.Text
    
    				If Not customers.AddNew(Customer) Then
    					MessageBox.Show(customers.ValidationMessage)
    					Return
    				Else
    '                    
    '                     * We will access the new customer back in the calling form so we
    '                     * need the new key.
    '                     
    					Customer.CustomerIdentifier = customers.NewIdentifier
    					DialogResult = System.Windows.Forms.DialogResult.OK
    					Close()
    				End If
    			Else
    				Customer.CompanyName = txtCompanyName.Text
    				Customer.ContactName = txtContactName.Text
    				Customer.ContactTitle = cboTitles.Text
    				Customer.Address = txtAddress.Text
    				Customer.Country = cboCountry.Text
    				Customer.City = txtCity.Text
    				Customer.PostalCode = txtPostalCode.Text
    
    				If Not customers.UpdateCustomer(Customer) Then
    					MessageBox.Show(customers.ValidationMessage)
    					Return
    				Else
    					DialogResult = System.Windows.Forms.DialogResult.OK
    				End If
    			End If
    		End Sub
    	End Class
    End Namespace


    Undue pending changes

    Public Shared Sub UndoPendingChanges(ByVal context As DbContext)
    	'detect all changes (probably not required if AutoDetectChanges is set to true)
    	context.ChangeTracker.DetectChanges()
    
    	'get all entries that are changed
    	Dim entries = context.ChangeTracker.Entries().Where(Function(e) e.State <> EntityState.Unchanged).ToList()
    
    	'somehow try to discard changes on every entry
    	For Each dbEntityEntry In entries
    		Dim entity = dbEntityEntry.Entity
    
    		If entity Is Nothing Then
    			Continue For
    		End If
    
    		If dbEntityEntry.State = EntityState.Added Then
    			Dim set = context.Set(entity.GetType())
    			set.Remove(entity)
    		ElseIf dbEntityEntry.State = EntityState.Modified Then
    			'entity is modified... you can set it to Unchanged or Reload it form Db??
    			dbEntityEntry.Reload()
    		ElseIf dbEntityEntry.State = EntityState.Deleted Then
    			'entity is deleted...
    			dbEntityEntry.State = EntityState.Modified
    		End If
    	Next
    End Sub

    Add a new record

    Public Property NewIdentifier() As Integer
    Public Function AddNew(ByVal NewCustomer As Customer) As Boolean
    
    	Using context As New NorthWindEntities()
    		Try
    			context.Customers.Add(NewCustomer)
    			context.SaveChanges()
    			NewIdentifier = NewCustomer.CustomerIdentifier
    			ValidationMessage = ""
    
    			Return True
    
    		Catch e1 As Exception
    			ValidationMessage = "Failed adding new customer."
    
    			Return False
    
    		End Try
    	End Using
    End Function

    Filtering

    Public Sub CompanyNameFilter(ByVal value As String, ByVal options As FilterOptions)
    	Using context As New NorthWindEntities()
    		Select Case options
    			Case FilterOptions.StartsWith
    				DataSource = context.Customers.Filter(Function(cust) cust.CompanyName.ToLower().StartsWith(value.ToLower(), StringComparison.CurrentCulture)).ToList()
    
    			Case FilterOptions.Contains
    				DataSource = context.Customers.Filter(Function(cust) cust.CompanyName.ToLower().Contains(value.ToLower())).ToList()
    
    			Case FilterOptions.EndsWith
    				DataSource = context.Customers.Filter(Function(cust) cust.CompanyName.ToLower().EndsWith(value.ToLower(), StringComparison.CurrentCulture)).ToList()
    
    			Case FilterOptions.Equals
    				DataSource = context.Customers.Filter(Function(cust) cust.CompanyName.Equals(value, StringComparison.OrdinalIgnoreCase)).ToList()
    
    			Case Else
    		End Select
    	End Using
    End Sub

    Caveat, when displaying data from Entity Framework or LINQ to SQL in a DataGridView data is not sortable unless a special component is used which can be found in my MSDN code sample (in vb.net) working with Entity Framework. Special note, I use a DTO (Data Transfer Object) which is not seen much in VB.NET.

    So there are some basics for working with Entity Framework 6 in a windows forms desktop project.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 10, 2018 12:13 PM
    Moderator
  • In the general way you ask it. In fact not. You can update a database on SQL Server with Linq to Entities and Linq to SQL (was an intermediate format because Linq to Entities was not ready at the moment of introduction. )

    Be aware that both need a very good normalize database to take advantage from it. 

    If you have that. Then take the replies from both other contributors. 

    https://en.wikipedia.org/wiki/Database_normalization


    Success
    Cor


    Saturday, March 10, 2018 12:30 PM
  • Which would be simpler? Would it be using a query builder instead?  They use the Fill By method often.

    Maybe using the Query Builder within the VB.net IDE would be easier for me since I may have known so much about statements from Access SQL.  I use Microsoft Access a lot.

    Regards,

    JohnDBCTX


    jp

    Saturday, March 10, 2018 12:30 PM
  • Read my reply, it passed each other I assume. But I was expecting this. 

    Success
    Cor

    Saturday, March 10, 2018 12:32 PM
  • Which would be simpler? Would it be using a query builder instead?  They use the Fill By method often.

    Maybe using the Query Builder within the VB.net IDE would be easier for me since I may have known so much about statements from Access SQL.  I use Microsoft Access a lot.

    Regards,

    JohnDBCTX


    jp

    Yes there are easier methods but that does not mean that they are equal or better than using Entity Framework. Using the query builder in Visual Studio is appealing because it is easy to use yet down the road many find this method troublesome. It's old and only good to dip your feet into working with data.

    You are much better off (if Entity Framework is too much for you at this time) is using SqlClient or OleDb data providers dependent on the backend database. If this route seem right for you then first learn how to write SQL as in my MSDN code sample. Next up, plan out your project. As you are writing code at the same time write unit test, see my primary code sample/article.

    Dependent on your data flow, learn to work not just with clean data but bad data as described in my code sample.

    Here is a code sample I did as a primer on working with data in general done in ms-access but the patterns shown are for any database e.g. ms-access, sql-server etc.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by JohnDBCTX Saturday, March 10, 2018 1:01 PM
    Saturday, March 10, 2018 12:48 PM
    Moderator
  • Can users trigger an event with a command button just to append records to a database using either SQL or LINQ?

    Regards,

    JohnDBCTX


    jp

    Saturday, March 10, 2018 12:52 PM
  • Are there more complicated steps in regards to appending records in a database by using the Query Editor than by using the Entity Framework?

    Regards,

    JohnDBCTX


    jp

    Saturday, March 10, 2018 12:56 PM
  • This needs some further research on how to export an Access SQL statement in the Access for Developers forum.

    Regards,

    JohnDBCTX


    jp

    Saturday, March 10, 2018 1:00 PM
  • This needs some further research on how to export an Access SQL statement in the Access for Developers forum.

    Regards,

    JohnDBCTX


    jp

    Perhaps the following clinical example may help. It's been laid out to focus solely on adding a new record to a ms-access table along with returning the new primary key (which many developers miss the part about returning the new key).

    In the example I use a class instance to add the record.

    While the alternative is to pass each field value in as shown below. Take special note how I construct the SQL and the use of parameters.

    Public Class Operations 
        Private Builder As New OleDb.OleDbConnectionStringBuilder With 
            { 
                .Provider = "Microsoft.ACE.OLEDB.12.0", 
                .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.
                                      BaseDirectory, "Database1.accdb") 
            } 
     
        Private InsertStatement As String = 
        <SQL> 
        INSERT INTO Customer  
            ( 
                CompanyName, 
                ContactName, 
                ContactTitle 
            )  
        Values 
            ( 
                @CompanyName, 
                @ContactName, 
                @ContactTitle 
            ) 
        </SQL>.Value 
     
    
        Public Function AddNewRow(
            ByVal CompanyName As String, 
            ByVal ContactName As String, 
            ByVal ContactTitle As String, 
            ByRef Identfier As Integer) As Boolean 
    
            Dim Success As Boolean = True 
     
            Try 
                Using cn As New OleDb.OleDbConnection(Builder.ConnectionString) 
                    Using cmd As New OleDb.OleDbCommand("", cn) 
     
                        cmd.CommandText = 
                            <SQL>
                                INSERT INTO Customer 
                                (
                                    CompanyName,
                                    ContactName,
                                    ContactTitle
                                ) 
                                Values 
                                (
                                    @CompanyName,
                                    @ContactName,
                                    @ContactTitle
                                )
                            </SQL>.Value
    
     
                        cmd.Parameters.AddWithValue("@CompanyName", 
                                                    CompanyName) 
    
                        cmd.Parameters.AddWithValue("@ContactName", 
                                                    ContactName) 
    
                        cmd.Parameters.AddWithValue("@ContactTitle", 
                                                    ContactTitle) 
     
                        cn.Open() 
     
                        cmd.ExecuteNonQuery() 
     
                        cmd.CommandText = "Select @@Identity" 
                        Identfier = CInt(cmd.ExecuteScalar) 
     
                    End Using 
                End Using 
     
            Catch ex As Exception 
                Success = False 
            End Try 
     
            Return Success 
     
        End Function 
    End Class 

    Take a ways from this code

    • By using <SQL> you can write the query if needed in ms-access, make sure it works then copy and paste into the <SQL> tag without any string concatenation.
    • The most common reason people say to use parameters is for defense against SQL injection while in desktop apps that is not very likely. The big reason (as shown) is the AddWithParameter method 99.99 percent of the time figures out the proper data type to inject into a query. Secondly let's say a value is a string with a single apostrophe. Without using parameters you must manually escape this character while using parameters this is done for you.
    • The method returns the primary key which can be used later to reference the newly added record e.g. for edit or delete or searching. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 10, 2018 1:30 PM
    Moderator
  • Here is a screenshot of what I am trying to accomplish:

    So far, I would need to use the append query and store it in a procedure.

    Regards,

    JohnDBCTX

    An example of how could I use this append query in SQL.


    jp

    Saturday, March 10, 2018 2:11 PM
  • Here is a screenshot of what I am trying to accomplish:

    So far, I would need to use the append query and store it in a procedure.

    Regards,

    JohnDBCTX

    An example of how could I use this append query in SQL.


    jp


    I am certain that I would need to use parameters after I would modify the append query in Query Builder dialog.

    jp

    Saturday, March 10, 2018 2:14 PM
  • In addition, the next screenshot demonstrates whether I would use the Insert method.

    I do hope now I may have concluded that using the Query Builder dialog would be a waste of time.

    Whereas, I would use the Insert method, which its senses three parameters to fill in.

    Regards,

    JohnDBCTX 


    jp

    Saturday, March 10, 2018 2:24 PM
  • Not sure what you mean by storing it into a procedure as they are stored in the designer file (in the .xsd file) e.g.

    Me._adapter.InsertCommand.CommandText = "INSERT INTO `Customers` (`CompanyName`, `ContactName`, `ContactTitle`, `Address`,"& _ 
        " `City`, `PostalCode`, `Country`, `Phone`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"

    Please note that you can not get the new primary key back when working with ms-access using TableAdapter method while the method I show allows you to get the new primary key. To get the new primary key with TableAdapter you must write a bunch of code or reload the data same as in the code generated in form load when you added data to a form.

    Remember I said using the path is troublesome down the road, it can also be troublesome at the start of using TableAdapters.

    Lastly, note the ? characters, this is because ms-access parameters are positional meaning if a field named FirstName is the first field in the insert statement and BirthDay is the next field you must have FirstName as the first parameter value followed by BirthDay and reversing them will cause a runtime exception while with SQL-Server the are named based parameters.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 10, 2018 2:29 PM
    Moderator
  • In addition, the next screenshot demonstrates whether I would use the Insert method.

    I do hope now I may have concluded that using the Query Builder dialog would be a waste of time.

    Whereas, I would use the Insert method, which its senses three parameters to fill in.

    Regards,

    JohnDBCTX 


    jp

    Yes, TableAdapter method is a waste of time as mentioned in my first reply :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by JohnDBCTX Saturday, March 10, 2018 2:41 PM
    Saturday, March 10, 2018 2:36 PM
    Moderator
  • SQL coders need to conduct more research before they can bridge that paradox issue.

    If there is more than one method on how to perform this in SQL, then it should be done with the keywords,

    PARAMETERS, and VALUES following the named parameters as I have mentioned in the screenshots.

    Regards,

    JohnDBCTX


    jp

    • Marked as answer by JohnDBCTX Saturday, March 10, 2018 2:45 PM
    Saturday, March 10, 2018 2:45 PM
  • Would it be efficient if I can import the SQL Server libraries just to interface with them?  Besides, I believe they

    have more built-in functions.

    Regards,

    JohnDBCTX


    jp

    Saturday, March 10, 2018 3:01 PM
  • SQL coders need to conduct more research before they can bridge that paradox issue.

    If there is more than one method on how to perform this in SQL, then it should be done with the keywords,

    PARAMETERS, and VALUES following the named parameters as I have mentioned in the screenshots.

    Regards,

    JohnDBCTX


    jp

    One point to be made here with naming conventions. In particular is with using ID as a name for a primary key. It's possible and we do this with databases that have upwards to thirty tables, each primary key is named as follows CustomerIdentifier, OrdersIdentifier etc. then for us (and many will do this) we have a base class to handle data operations where the read, update, add and delete use one class rather than a class for each table via generics coupled with a Interface where the Interface forces each class e.g. Customers and Orders to have a Id field. Why do this? Because in the SQL it's easier to distinguish primary keys yet one may say this is the job for aliasing in SQL, sure but it's clearer and we can use generics to consolidate action methods such as CRUD.

    I believe many ms-access developers are use to using ID for a primary key as that is the default when creating a new table yet we need to move past this as our projects grow in complexity.

     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 10, 2018 3:05 PM
    Moderator
  • Or perhaps import those Microsoft Office Interop commands from Access. Maybe that would be a better idea for 

    my coding experience and knowledge in Access SQL.

    What are your responses to your questions?

    Regards,

    JohnDBCTX


    jp

    Saturday, March 10, 2018 3:06 PM
  • In the near future, the developers should perform more research on how to bridge these gaps

    among SQL, LINQ, VB.NET, Access, and so forth.  I am correct on that solution presently.

    Regards,

    JohnDBCTX


    jp

    Saturday, March 10, 2018 3:12 PM
  • In the near future, the developers should perform more research on how to bridge these gaps

    among SQL, LINQ, VB.NET, Access, and so forth.  I am correct on that solution presently.

    Regards,

    JohnDBCTX


    jp

    What the developer should understand is database administration 101(DBA 101) and T-SQL (T-SQL 101)  with T-SQL doing Create, Read Update and Delete (CRUD)  operations with the database table's records. 

    And that's with any database such as Access, MS SQL Server or otherwise. And solutions such as Linq generate T-SQL that is submitted to the database engine for execution. If one doesn't know the basic 101 on said technologies, then how can one use them effectively?

    Saturday, March 10, 2018 3:27 PM
  • Or perhaps import those Microsoft Office Interop commands from Access. Maybe that would be a better idea for 

    my coding experience and knowledge in Access SQL.

    What are your responses to your questions?

    Regards,

    JohnDBCTX


    jp

    No a wise choice to bring in Interop commands. Projects created in Visual Studio that access data are best done with a) native data provider OleDb generally done with a connection and command objects where a data reader feeds from the command object. Going this route nothing changes while using Interop commands you are now dependent on MS-Access not changing something.

    In regards to your experience with Access SQL, that was me many years ago but jumped off that ship a very long time ago in favor of native OleDb data provider. 

    Side note sometime back in 2010 I wrote a code sample using ms-access (a .mdb at that time) coupled with OleDb to extract SQL statements from access and run them in .NET. Generally speaking it's best to avoid Interop libraries be it Access or Excel, there are better ways. Off topic, with Excel it's best to use OpenXML rather than Excel automation.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 10, 2018 3:40 PM
    Moderator
  • Would it be efficient if I can import the SQL Server libraries just to interface with them?  Besides, I believe they

    have more built-in functions.

    Regards,

    JohnDBCTX


    jp

    What libraries? and why as SQL Server libraries don't work with MS-Access databases. OleDb is for Access and SqlClient provider is for SQL-Server.

    Both are available but no need to use SQL Server libraries if using MS-Access.

    Have you if possible considered SQL-Server as there are many benefits including stored procedures and various forms of transactions.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 10, 2018 3:42 PM
    Moderator
  • Of all of the SQL types:  Does SQL Server provides creating function routines prior to file importing?  

    Besides, at one time, I may have downloaded the SQL Server versions; however, there were numerous 

    version conflicts.

    I need to know if there is an express version of Microsoft SQL Server for download and installation?

    Regards,

    JohnDBCTX


    jp

    Saturday, March 10, 2018 3:55 PM
  • Of all of the SQL types:  Does SQL Server provides creating function routines prior to file importing?  

    Besides, at one time, I may have downloaded the SQL Server versions; however, there were numerous 

    version conflicts.

    I need to know if there is an express version of Microsoft SQL Server for download and installation?

    Regards,

    JohnDBCTX


    jp

    This question desired a completely new thread/post. When doing so please explain what you mean by "creating function routines prior to file importing" as this is not clear to me and I'm sure others.

    What I will answer is always download the current version unless a customer can not have said version installed. You will also want SQL-Server Management Studio. SSMS is essential for development while without this tool life will be difficult unless you are fluent with T-SQL.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, March 10, 2018 4:36 PM
    Moderator
  • Karen, 

    Maybe is this a good example where the forums did go wrong. As Reed has shown in his message around etiquette there is a conflict. 

    You can assume that the OP is not able to state his problem well in English and give a solution the OP really needs. It is arbitrary but you know I don't do that or I must be sure nobody can get misleaded by the question.

    However, I think that then at least a moderator must change in a way the Questions header to avoid that endless persons have to read this whole thread if they search in on Internet this question

    (I know that MSFT do give those answers as well and are probably instructed like in the answer forums that it is not important to give a good answer as long as there is an answer.) 


    Success
    Cor

    Saturday, March 10, 2018 6:17 PM