VBA/Excel Connecting Excel to Access using VBA

Video demonstrates how to pull directly from Access and put to an Excel worksheet.

Category:

Education

Tags:

• excel
• vba
• visual
• basic
• advanced
• macros
• databases
• access
• sql
• server
• programming
• ado
• recordset
• .NET
• tricks
• tips

[democritus74]

Hello.

What if the database is *.accdb ? It doesn't recognize con.Open

[exceltip]

@democritus74

example:

Provider=Microsoft.ACE.OLEDB.1­2.0;Data Source=C:\myFolder\myAccess200­7file.accdb;Persist Security Info=False;

[sandhollownight]

Whats the syntax if my db has a login and password?

[exceltip]

Put this in the connection string (change password and username accordingly):

;Uid=Admin;Pwd=mypassword;

[exceltip]

Below is the connection string (can be hard to read):

'---------------------------

Dim con As New ADODB.Connection

Dim rc As New ADODB.Recordset

'Drive code for access

con.ConnectionString = "DBQ=c:\temp\db1.mdb; " & _

"DRIVER={Microsoft Access Driver (*.mdb)}"

'Open the connection

con.Open

Set rc.ActiveConnection = con

[exceltip]

You need to select "WATCH IN HIGH QUALITY."

[TheMaxkuba]

Hi we developed a software for this: "software-development.iga-t.co­m", regards, Max

[TheMaxkuba]

Hey you can also use this Add-In for this task: software-development.iga-t.com­/shop/software/excel-2-access-­detail.html

[garmit61]

Great tutorial and fabulous music. Who's the song by and what's it called?

[123japones]

VERSION OFFICE 2010 Dim con As New ADODB.Connection Dim rs As New ADODB.Recordset con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.­12.0;Data Source=C:\Users\nelson.barreto­\Documents\BASE.accdb;Persist Security Info=False;" 'Open Db connection con.Open Set rs.ActiveConnection = con rs.Open "Select * from cliente" StartRow = 3 Do Until rs.EOF Cells(StartRow, 4) = rs.Fields(0).Value rs.MoveNext StartRow = StartRow + 1 Loop Set rs = Nothing con.Close Set con = Nothing

[123japones]

THE BESTTTTT

MUITO BOM

[yoshikodomo]

Hello.

I'm having problem with this part, the driver code:

con.ConnectionString = "DBQ=d:\my_db.mdb;" &

"DRIVER={Microsoft Access Driver (*.mdb)};"

You see, I add semicolon after the bracket on the *.mdb because that's what I saw in your video though what you posted in your comment did not have one. But either it has semicolon or not, I still get an error and that code that I've posted is of course in red.

Please help me with this. Thanks!

[aceaceaceJOKER]

I figured it out. Just edit the recordset part with this. Define column at the top. StartRow = 1 For column = 0 To rs.Fields.count - 1 Cells(StartRow, column + 1) = rs.Fields(column).Name Next column = 0 StartRow = 2 Do Until rs.EOF For column = 0 To rs.Fields.count - 1 Cells(StartRow, column + 1) = rs.Fields(column).Value Next rs.MoveNext StartRow = StartRow + 1 Loop

[aceaceaceJOKER]

Thanks for the tutorial Exceltip. I've been trying to figure out how you would code it to where you would include all the column names at the top of the spreadsheet and list all the data rows under each column name. Please help thanks.