Open access database from excel vba Results

I'm trying to find a way to launch an access database using Excel VBA. Does
someone have code which will do this.

Thanks,
Manuel

Is it possible to open an Access database from a form in Excel?

the Access database is located here:

C:oceandatabasescrimpers.mdb

I want to click a button on a form in Excel and open this Access database.

Thanks for any help.

Excel 2000
Access 2000

I’ve written a macro to search through an Excel pricelist, compare currencies and prices and give me the best price and corresponding vendor for a list of products. Only trouble is that my data has grown exponentially in the last week and I currently have more than 65000 rows. I imported the data into Microsoft Access and would like to use my search VBA algorithm.

How do I do that? How do I open the Access Database from Excel and then apply my current code to it. I’m using ranges and rows – what I basically do is locate the first occurrence of the sought product, save its price, set a new search range to exclude the first occurrence, search for other rows containing the product and compare their prices to the first one and save the smallest of all values.

I really have no idea how to do that in Access. Any help will be appreciated!

Thanks
Nick

I am able to connect to a specific Access table called "Metric" from Excel VBA using an ADO, but I am having difficulty updating a field in the table based on an Excel spreadsheet criteria. I have an Excel spreadsheet that has a list of Labels in a range that may or may not have a yellow Interior. A yellow interior indicates that I need to update the Access table field called "HideOnMerge". This field is the 20th field in the table. I am trying to use a Do Loop to move through each cell in a range and first Find if the Label exists in the database then update the table field "HideOnMerge".
My code is below and the code breaks at this specific point.

"rstTable.Find ActiveSheet.Cells(i, 4).Value"

Should I be using an SQL...


	VB:
	
 UpdateAccessDB() 
    Dim rstTable As ADODB.Recordset 
    Dim cnn As ADODB.Connection 
    Dim WrkBk As Workbook 
    Dim strAccessPath As String 
    Dim i As Long 
     
    Set cnn = New ADODB.Connection 
    Set rstTable = New ADODB.Recordset 
    Set WrkBk = ThisWorkbook 
    strAccessPath = "C:taxo_auth_4.3.mdb" 
     
    cnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strAccessPath & ";" & _ 
    "Jet OLEDB:Database Password=happy;" 
    cnn.Open 
    rstTable.Open "Metric", cnn, adOpenDynamic 
     
    If rstTable.BOF = False Then 
        rstTable.MoveFirst 
    End If 
     
    i = 2 
    Do Until WrkBk.ActiveSheet.Cells(i, 4).Value = "" 
        rstTable.Find ActiveSheet.Cells(i, 4).Value 
        If rstTable.EOF = True Then 
            rstTable.AddNew 
            rstTable.Fields("Label") = WrkBk.ActiveSheet.Cells(i, 4).Value 
            If WrkBk.ActiveSheet.Cells(i, 4).Interior.ColorIndex = 6 Then _ 
            rstTable.Fields("HideNoMerge").Value = True 
        End If 
        i = i + 1 
    Loop 
     
    rstTable.Update 
    rstTable.Close 
    Set rstTable = Nothing 
    cnn.Close 
    Set cnn = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Any help is greatly appreciated!!

Edit: Added code tags (WillR)

hi all,
may i know the follow to connect to database able to define in VBA (excel)?
i tried to use but when run i got the message compile error: user defined
type not defined at Dim rstest as adodb.recordset
if this cannot be done in VBA, what is the alternative to access database
from excel?
please advise.
thank you very much

Public cnsql As ADODB.Connection
Sub Main()
Set cnsql = New ADODB.Connection
With cnsql
..Provider = "xx;" & _
"Data Source=xx;" & _
"Initial Catalog=xx;" & _
"User Id=xx;" & _
"Password=xx"
..Open
End With
End Sub

private sub
Dim rstest As ADODB.Recordset
Set rstest = New ADODB.Recordset
With rstest
..ActiveConnection = xx
..CursorType = adOpenKeyset
..LockType = adLockOptimistic
..Open "sql statement"'"
End With

Hello, I am opening and running an Access macro from Excel VBA which works perfectly, but each time Access is opened even though it is invisible, it still prompts the user to Cancel, Open, or More Info. I've tried putting all the warnings and alerts on false, but it still opens this prompt. This is a problem because the excel macro does other things before it gets to this point and I don't want the user to have to wait around to press Open before the macro will continue. I've considered using SendKeys, but we all know this is very unreliable. Here's my Excel VBA code. Thanks!


	VB:
	
 Run_Data() 
     
    Dim SrceFile 
    Dim DestFile 
    Dim dbs As Object 
     
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
    Application.DisplayAlerts = False 
    DoCmd.SetWarnings = False 
     
     'Copy old database to new location
    SrceFile = Range("DBpath").Value 
    DestFile = Range("DestPath").Value 
    FileCopy SrceFile, DestFile 
     
     'Run monthly process at new location
    Set dbs = CreateObject("Access.Application") 
    dbs.Visible = False 
    dbs.OpenCurrentDatabase (DestFile) 
    dbs.DoCmd.RunMacro "RunMonthlyProcess" 
    dbs.CloseCurrentDatabase 
    dbs.Quit 
    Set dbs = Nothing 
     
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
    DoCmd.SetWarnings = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


hi all,
may i know the follow to connect to database able to define in VBA (excel)?
i tried to use but when run i got the message compile error: user defined
type not defined at Dim rstest as adodb.recordset
if this cannot be done in VBA, what is the alternative to access database
from excel?
please advise.
thank you very much

Public cnsql As ADODB.Connection
Sub Main()
Set cnsql = New ADODB.Connection
With cnsql
..Provider = "xx;" & _
"Data Source=xx;" & _
"Initial Catalog=xx;" & _
"User Id=xx;" & _
"Password=xx"
..Open
End With
End Sub

private sub
Dim rstest As ADODB.Recordset
Set rstest = New ADODB.Recordset
With rstest
..ActiveConnection = xx
..CursorType = adOpenKeyset
..LockType = adLockOptimistic
..Open "sql statement"'"
End With

Hello All,
I am trying to create a new Access table from Excel VBA using ADO connection. When I run my code below I get no errors, it seems to go run completely, but there is no new table created. Any help would be greatly appreciated!


	VB:
	
 IndustrySpecificMetric() 
    Dim cnt As ADODB.Connection 
    Dim rst As New ADODB.Recordset 
    Dim stSQL As String 
    Dim stSQLEx As String 
    Dim stCon As String 
    Dim stAccessPath As String 
    Dim vtMetric As Variant 
    Dim intRow As Integer 
    Dim i As Integer 
     
    vtMetric = Array("Tagged Metrics", "Validation Formulas") 
     
    Range("A1").EntireColumn.Insert 
    intRow = Range("A9").CurrentRegion.Rows.Count - 1 
    Range("A10").Offset(0, 0).Resize(intRow, 1).Formula = "=LEFT(RC[1],FIND("":"",RC[1])-1)" 
    Range(Range("A10"), Cells(9 + intRow, 1)).Name = "rngTax" 
    Range(Range("B10"), Cells(9 + intRow, 2)).Name = "rngMetric" 
    Range("rngTax").Copy 
    Range("A10").PasteSpecial (xlValues) 
    Application.CutCopyMode = False 
     
     
    stAccessPath = "C:taxo_auth_4.3 (2).mdb" 
     
     'Indicates Database filepath
    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & stAccessPath & ";" & "Jet OLEDB:Database Password=brasil;" 
     
    Set cnt = New ADODB.Connection 
    Set rst = New ADODB.Recordset 
     
     'Establishes Connection
    cnt.Open stCon 
     
    stSQL = "CREATE TABLE IndustrySpecificMetric MetricID, TaxonomyID, Setting, PRIMARY KEY (MetricID)" 
     
     'Loops thru MetricID range
    For Each rngCell In Range("rngMetric") 
        stSQL = "SELECT * FROM IndustrySpecificMetric" 
         
        On Error Resume Next 
        With rst 
            stSQLEx = "UPDATE IndustrySpecificMetric" & _ 
            " SET MetricID = '" & rngCell.Value & "'" & _ 
            " SET TaxonomyID = '" & rngCell.Offset(0, -1).Value & "'" & _ 
            " SET Setting = '" & rngCell.Offset(0, 8).Value & "'" 
            .Open stSQL, cnt, 3, 3, adCmdText 
            cnt.Execute stSQLEx 
            .Close 
        End With 
        stSQL = Empty 
         
    Next rngCell 
    On Error Goto 0 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am simply trying to open an Access database from Excel. I have some VBA code that doesn't quote work right. It opens Access up and then closes it automatically. I would like the user to be able to use the Access database. Here's the code I used.

Dim DPath As String
LPath = "C:DatabaseDb2.mdb"

Set oApp = CreateObject ("Access.Application")
oApp.Visible = True

oApp.OpenCurrentDatabase DPath

Thanks for your help!

I have an excel spreadsheet and I would like to know whether or not it is
possible to open an Access database from excel using VBA?

Any help would be appreciated.

Many thanks,

Kieron White

I already know how to open an instance of Access using the
application.ActivateMicrosoftApp = xlMicrosoftAccess. But what I havent been
able to get is a way to just open a specific database from excel vba. I dont
want to edit data from access to excel or vice versa. I just want a button
that says start this specific database. The data base is stored under
"C:DataAccessCustomerCare.mdb". Is any one able to help me with this.

Hi all, i have to delete some record from access database, at the moment i can insert and view data with this code:

For insert:
PercFile = ActiveWorkbook.Path _
& "db1.mdb"
Set db = OpenDatabase(PercFile)
Set rs = db.OpenRecordset("Table", dbOpenTable)
Do While Cells(a ,b) > 0 then
With rs
.AddNew
.Fields("Data") = Cells(a, b)
.Update
End With
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

For view:
PercFile = ActiveWorkbook.Path _
& "db1.mdb"
conn.Open ConnectionString:= _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & PercFile
rst.Open "Table", conn, adOpenStatic
With rst
Do While Not .EOF
If rst.Fields("Number")=n Then
Cells(a , b) = .Fields("Name")
End If
.MoveNext
Loop
End With
conn.Close

i have try to modify the code that insert a record, with a .Delete that replace the .AddNew method but i obtain an error

Some idea?
thanks in advance

If anyone out there can assist me I will be most grateful. I am trying to
open an Access mdb file from VBA within excel and to further open one of the
macros within the mdb file.

If it helps, the purpose of this is to udpate an Access database from some
extremely large spreadsheets from excel so that I can run some killer
financial reports from it. I find that sum-if and vlookups on such large
amounts of data are cumbersome in excel alone. So, I want to do the calcs in
Access through queries and return them into excel. But, as this spreadsheet
is going to be used by others, I want it to be seemless.

I can accomplish the task if I put the following string into Start/Run:

"C:Program FilesMicrosoft OfficeOffice11msaccess.exe" "C:Documents and
SettingsAdministratorMy DocumentsTest DirectoryExcel Access Test Db.mdb"
/Excl /X Macro1"

Please help.

Regards,

Layne

Hi,

I'm calling an Access database that we use from a custom menu in Excel via VBA. The syntax works perfectly, however when the window opens it is in a minimised state. I was just wondering if there was a line of code I could use that would maximise the window to the full screen on opening?

Sub CallDatabase()

    Dim LPath As String
    Dim LCategoryID As Long
    Dim oApp As Object

    LPath = "E:Access2010Database.mdb"

    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True

    oApp.OpenCurrentDatabase LPath
    
End Sub
Many Thanks in advance

Macro1

I have code set up to open an Access database from within Excel using a VB
module. It works, but now I want to add a password to the Access database
and I can not figure out how to do it. Below is the open routine. What do I
need to add to it so it opens it with a password.

Sub OpenMsAccess()
Dim AccessApp As Object

Set AccessApp = CreateObject("Access.Application")
AccessApp.Visible = True
AccessApp.OpenCurrentDatabase "C:TestDirTestDb.mdb"
'process data
AccessApp.Quit
Set AccessApp = Nothing

End Sub

Hi!

I'm trying to connect to Access database (from Excel) and get info with SQL. Could someone take a look at this code and tell me if there's some obvious mistake..

Private Sub Worksheet_Activate()

Range("A47:L82").Select

Set yhteys = New ADODB.Connection

cnn = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=blahblah;" & _
"Uid=;" & _
"Pwd="

yhteys.ConnectionString = cnn
yhteys.Open
Set rstTemp = New ADODB.Recordset
rstTemp.Open "SELECT * from Tilit", yhteys, adOpenForwardOnly, adLockReadOnly
rstTemp.MoveFirst
While Not rstTemp.EOF

rstTemp.MoveNext
Wend

rstTemp.Close
yhteys.Close

End Sub

Hello

Does anybody have a piece of standard code that will enable me to open an access database (97) from excel (2000)?

Thanks
Lloyd

Hi,

Having thoroughly (I think) searched the internet for solutions I'm thoroughly stumped.

I'm using Excel 2003 and Access 2003

I have a VBA Macro that selects, trims and then copies a range of data in my spreadsheet. It then creates a new database named according to the value in cell C2 in the spreadsheet. What I am ultimately trying to do is then insert/paste the copied data into a new table (called SURVEY) in the newly created Access database.

I can get, as you will see, the newly created Access database open but when I manually paste the data in (Edit>Paste), I don't get the option 'Does the first row of your data contain column headings?', which my data does not and the data then pastes into the new table incorrectly. If I have to manually paste the data, I have to close access down and re-open it before I get that option.

My knowledge of VBA / Macros is limited; I'm taking a 'throw myself in the deep end and feel my way around' approach but will apply myself to learning the ins and outs of any solutions proposed.

Desired outcomes: One of two :-
Either - the last bit of code to automatically insert the selected data into a new Access table
Or - A way of ensuring that my manually pasted data isn't treated as if the first row contains column headings.

Code I am using:
(disclaimer: I've written very little of this myself, the majority is cannibalised from the results of my internet searches)

Code:
 
Sub InsertInto()
    Dim dbConnectStr As String
    Dim Catalog As Object
    Dim cnt As ADODB.Connection
    Dim dbPath As String
    
    For Each CELL In [B1:C360]
    CELL.Value = WorksheetFunction.Trim(CELL)
    Next CELL
    
    Range("A1:I360").Select
    Selection.Copy
    'Set database name here
    dbPath = "D:Uploaded" & Range("C2") & ".mdb"
    dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
    'Create new database
    Set Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr
    Set Catalog = Nothing
    'Open Access and make visible
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True
    'Open Access database as defined by LPath variable
    oApp.OpenCurrentDatabase dbPath
End Sub
Thank you in advance for any help you're able to give.

Hi all, I have the following code which I run from Excel. It opens an access database, runs two macros and then opens the recordset. This code works very well.

However, I now need to copy the entire contents of the recordset in "Results Table" to a specific cell in Excel (A2). I have looked on the board as well as others, but cannot see a simple solution to this. Does anyone have any ideas please?

Code:
Sub RunAccessMacro_RunReport()
    Set AccApp = GetObject("c:Property 08.mdb")

    AccApp.DoCmd.RunMacro "UpdateData"
    AccApp.DoCmd.RunMacro "CreateResults"
    AccApp.DoCmd.OpenTable "Results table", acViewDesignNormal
       
    AccApp.DoCmd.Quit
    MsgBox "Done"
End Sub


What is the VBA code for opening an existing ACCESS database from the VBE ?
I can't find any specific reference to this when I google it.

Thanks in advance for any assistance.