VBA to import data.

[pedie]

Hi, i need simple VBA to import data from access file path "E:\Personal_Files\Access\Database2.accdb", table name "Datas", all fields to sheet one.


Thanks in advance

Pedie

I tried below code with above path and errors....unrecognized format....
Please advice...

[/FONT]
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
strFilePath = "E:\Personal_Files\Access\Database2.mdb"
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
 
Sheet1.Range("A1").ClearContents
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";"
 
sQRY = "SELECT Datas.* FROM Datas"
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Sheet1.Range("A1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub

 

[Darkspartan]

Hi Pedie,
it might be easiest if you just record the macro.

Turn on the recorder, go to data - import external data - import data

This will start a wizard that will ask you to select your file and tables/queries

 

[pedie]

I did that and works but i want to know the other way also...

Thanks again.

 

[Darkspartan]

Hi, i need simple VBA to import data from access file path "E:\Personal_Files\Access\Database2.accdb", table name "Datas", all fields to sheet one.


Thanks in advance

Pedie

I tried below code with above path and errors....unrecognized format....
Please advice...

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
strFilePath = "E:\Personal_Files\Access\Database2.mdb"
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
 
Sheet1.Range("A1").ClearContents
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";"
 
sQRY = "SELECT Datas.* FROM Datas"
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Sheet1.Range("A1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub

Edit: I wont be able to help with that :-\

Sub import()
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=E:\Personal_Files\Access\Database2.accdb;Mod" _
, _
"e=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password=""""" _
, _
";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transaction" _
, _
"s=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't C" _
, _
"opy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Datas")
.Name = "Database2.accdb"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "E:\Personal_Files\Access\Database2.accdb"
.Refresh BackgroundQuery:=False
.MaintainConnection = False
End With

 

[pedie]

Thanks again DarkSpartan for helping....

i also came up with something like this earlier...
I will appriciate if I get more inputs here...


Thanks again.

[/FONT]
[FONT=Courier New]Sub TEST2_ACCESSDATAImportMethod()[/FONT]
[FONT=Courier New]Dim myfile As String
myfile = "E:\Personal_Files\Access\Database2.accdb"[/FONT]

[FONT=Courier New]Sheet1.Cells.Delete Shift:=xlUp
Range("A1").Select[/FONT]

[FONT=Courier New]    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & myfile & ";Mode=Read;Exten" _
        , _
        "ded Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=6" _
        , _
        ";Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Databa" _
        , _
        "se Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=Fa" _
        , _
        "lse;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
        ), Destination:=Sheet1.Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("Datas")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "E:\Personal_Files\Access\Database2.accdb"
        .ListObject.DisplayName = "Mytab1"
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.ListObjects("Mytab1").Unlist
End Sub

 

[pedie]

Somehow, i think my last post code is locking up database...
could someone pls recheck...?