Howw do I Open MS Access and a database from Excel 1

[kyleo]

I'm new so please be patient.
I've written procedures in excel to manipulate data. Now I want to open access and open a database using the excel vba code.

here is the code i have written.

Sub OpenAccess()
Dim AccApp As Object
Dim AccDb As Database

Set AccApp = CreateObject("Access.Application&quot
AccApp.Visible = True

Set AccDb = OpenDatabase("c:\Chrono\CustomerProfileTest500.mdb&quot
End Sub

What happens is access momentairly appears then disappears from the screen as if it opened and then closed immediately.

Thanks for any help you can give me in getting past this.

 

[DarkSun]

Here is some code which opens a database then queries a table and then populates an array.


Dim VodaDB As Database ' Creates Database object
Dim VodaRS As DAO.Recordset ' Creates a recordSet Object


Private Sub OpenAccess()
Dim PhoneNumbers() As String
Dim PhoneNumbersEN As Integer

PhoneNumbersEN = 0

Set VodaDB = DBEngine(0).OpenDatabase("c:\voda\d2210f0t.mdb&quot
Set VodaRS = VodaDB.OpenRecordset("SELECT CTN FROM CTN&quot

Do While Not VodaRS.EOF
ReDim Preserve PhoneNumbers(PhoneNumbersEN)
PhoneNumbers(PhoneNumbersEN) = VodaRS!CTN
PhoneNumbersEN = PhoneNumbersEN + 1
VodaRS.MoveNext
Loop

End Sub

 

[jonscott8]

Hi Kyleo,

The reason the application momentarily displays and then disappears is because the variable, AccApp, is local to the OpenAccess procedure. Once the END SUB is executed, AccApp falls out-of-scope, thereby releasing your variable reference to Access.

You can get around this by adding the variable declaration to the General Declarations of the workbook, which will remain in-scope after the OpenAccess procedure. Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[kyleo]

Thank you Johnscott8 and Darksun. The code and explaination jump started me and I’ve gotten the Access started. Now I am stuck again. I need to pass a string variable from the Excel VBA code to the Access VBA code and then use that string in the Sub procedures that are working in Access.

(I’m really showing my ignorance now) As I understand it I can’t call the Access Sub procedures directly from the Excel VBA, Rather, I need to somehow use a Function Procedure to call the Sub procedures. And I need to pass the string through to Access. I’ve messed with this and realize I don’t understand how a function procedure works because I can’t even get one to call a sub procedure in access much less from excel.

If you guys could once again get me jump started I would really appreciate it. Your help is really great. I have nobody to talk to about this stuff.