robace


Can VBA code be written to modify records in a sql or access database I can write vba code to query a database very easily and pull out the data I need, but can I write back to that same source and modify/add records through vba




Re: Use excel to update records in a sql database or access

Shasur


You need to use ADO to achieve that

Code Snippet

Dim oCN As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String

Dim qt As QueryTable

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\sample.mdb;Persist Security Info=False"
Set oCN = New ADODB.Connection
oCN.ConnectionString = ConnString
oCN.Open

SQL = "Select * from Table"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCN
oRS.Open

' Do your updates here


If oRS.State <> adStateClosed Then
oRS.Close
End If

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCN Is Nothing Then Set oCN = Nothing

Try it out.. Good LuckSmile

Cheers

Shasur







Re: Use excel to update records in a sql database or access

robace

Thanks - I got it to work!






Re: Use excel to update records in a sql database or access

Student of IT

I have an excel file that is created from an application (so I can't link to it) that I need to import into an Access table to run reports and queries on. I need to know how to import this information so it will update the exisiting table. I know how to do this in vb.net, however, they want it done as part of Access. What code will replace/update the information in the table instead of trying to import all the info, where duplicate values are lost because of key violations.





Re: Use excel to update records in a sql database or access

Cringing Dragon

You're more likely to get a response if you start a new thread rather than adding on to an existing thread (especially one which has already been marked as answered). You can include a hyperlink to the existing thread if it's relevant.