Importing Data from Access using Excel VBA
Hope someone could help me. I have a macro that pulls a sample of data from Access. The access data first column [ID] is sorted numerically from 1 to the end of data. The macro below takes user's input (start point and end point) to get a sample. It works fine for the most part, but sometimes it returns the data not sorted as in the database. For example, if I enter start point 3,500 and end point 3,999. The first row of data in excel is 3533 to 3,627. Then the next row is 3,500 to 3,532. Then 3,628 to 3,999. Have no clue why would it do that. And sometimes it pulls everything as it should - but the problem is consistent at the same start/end points. I have went through the database and there is no issues with it. Thanks in advance.
Sub GetAccessData(StartofData As Long, EndofData As Long, WS As Worksheet, WB_Path As String)
Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Long
Dim x As Long
Application.ScreenUpdating = False
'DataBase Path
DBFullName = WB_Path & "RawData - Template.accdb"
'Open the Connection
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect
'Create a RecordSet
Set Recordset = New ADODB.Recordset
' Client-side cursor
Recordset.CursorLocation = adUseClient
With Recordset
Source = "SELECT * FROM CT_RawData WHERE [ID] BETWEEN " & StartofData & " AND " & EndofData
.Open Source:=Source, ActiveConnection:=Connection
'WS.Activate
On Error Resume Next
WS.Range("A3").CopyFromRecordset Recordset
'Sheets("Chart1").Activate
End With
End Sub
vba excel-vba ms-access
asked Nov 15 '18 at 23:23
M. MarzoukM. Marzouk
3911
add a comment |
Hope someone could help me. I have a macro that pulls a sample of data from Access. The access data first column [ID] is sorted numerically from 1 to the end of data. The macro below takes user's input (start point and end point) to get a sample. It works fine for the most part, but sometimes it returns the data not sorted as in the database. For example, if I enter start point 3,500 and end point 3,999. The first row of data in excel is 3533 to 3,627. Then the next row is 3,500 to 3,532. Then 3,628 to 3,999. Have no clue why would it do that. And sometimes it pulls everything as it should - but the problem is consistent at the same start/end points. I have went through the database and there is no issues with it. Thanks in advance.
Sub GetAccessData(StartofData As Long, EndofData As Long, WS As Worksheet, WB_Path As String)
Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Long
Dim x As Long
Application.ScreenUpdating = False
'DataBase Path
DBFullName = WB_Path & "RawData - Template.accdb"
'Open the Connection
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect
'Create a RecordSet
Set Recordset = New ADODB.Recordset
' Client-side cursor
Recordset.CursorLocation = adUseClient
With Recordset
Source = "SELECT * FROM CT_RawData WHERE [ID] BETWEEN " & StartofData & " AND " & EndofData
.Open Source:=Source, ActiveConnection:=Connection
'WS.Activate
On Error Resume Next
WS.Range("A3").CopyFromRecordset Recordset
'Sheets("Chart1").Activate
End With
End Sub
vba excel-vba ms-access
asked Nov 15 '18 at 23:23
M. MarzoukM. Marzouk
3911
1
Whether your data is sorted by ID in your source object is irrelevant. Add an ORDER BY clause to your SELECT statement and see if that makes a difference.
– Rene
Nov 15 '18 at 23:31
1
Yep, that did it. Pretty new to this. Thanks.Source = "SELECT * FROM CT_RawData WHERE [ID] BETWEEN " & StartofData & " AND " & EndofData & " ORDER BY [ID]"
– M. Marzouk
Nov 15 '18 at 23:35
Just out of curiosity - what was driving the odd sorting that was being pulled in.
– M. Marzouk
Nov 15 '18 at 23:43
I have seen this behavior too, even with just 500 lines of data.
– JonRo
Nov 15 '18 at 23:54
add a comment |
Hope someone could help me. I have a macro that pulls a sample of data from Access. The access data first column [ID] is sorted numerically from 1 to the end of data. The macro below takes user's input (start point and end point) to get a sample. It works fine for the most part, but sometimes it returns the data not sorted as in the database. For example, if I enter start point 3,500 and end point 3,999. The first row of data in excel is 3533 to 3,627. Then the next row is 3,500 to 3,532. Then 3,628 to 3,999. Have no clue why would it do that. And sometimes it pulls everything as it should - but the problem is consistent at the same start/end points. I have went through the database and there is no issues with it. Thanks in advance.
Sub GetAccessData(StartofData As Long, EndofData As Long, WS As Worksheet, WB_Path As String)
Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Long
Dim x As Long
Application.ScreenUpdating = False
'DataBase Path
DBFullName = WB_Path & "RawData - Template.accdb"
'Open the Connection
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect
'Create a RecordSet
Set Recordset = New ADODB.Recordset
' Client-side cursor
Recordset.CursorLocation = adUseClient
With Recordset
Source = "SELECT * FROM CT_RawData WHERE [ID] BETWEEN " & StartofData & " AND " & EndofData
.Open Source:=Source, ActiveConnection:=Connection
'WS.Activate
On Error Resume Next
WS.Range("A3").CopyFromRecordset Recordset
'Sheets("Chart1").Activate
End With
End Sub
vba excel-vba ms-access
asked Nov 15 '18 at 23:23
M. MarzoukM. Marzouk
3911
Hope someone could help me. I have a macro that pulls a sample of data from Access. The access data first column [ID] is sorted numerically from 1 to the end of data. The macro below takes user's input (start point and end point) to get a sample. It works fine for the most part, but sometimes it returns the data not sorted as in the database. For example, if I enter start point 3,500 and end point 3,999. The first row of data in excel is 3533 to 3,627. Then the next row is 3,500 to 3,532. Then 3,628 to 3,999. Have no clue why would it do that. And sometimes it pulls everything as it should - but the problem is consistent at the same start/end points. I have went through the database and there is no issues with it. Thanks in advance.
Sub GetAccessData(StartofData As Long, EndofData As Long, WS As Worksheet, WB_Path As String)
Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Long
Dim x As Long
Application.ScreenUpdating = False
'DataBase Path
DBFullName = WB_Path & "RawData - Template.accdb"
'Open the Connection
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Connect
'Create a RecordSet
Set Recordset = New ADODB.Recordset
' Client-side cursor
Recordset.CursorLocation = adUseClient
With Recordset
Source = "SELECT * FROM CT_RawData WHERE [ID] BETWEEN " & StartofData & " AND " & EndofData
.Open Source:=Source, ActiveConnection:=Connection
'WS.Activate
On Error Resume Next
WS.Range("A3").CopyFromRecordset Recordset
'Sheets("Chart1").Activate
End With
End Sub
vba excel-vba ms-access
vba excel-vba ms-access
asked Nov 15 '18 at 23:23
M. MarzoukM. Marzouk
3911
asked Nov 15 '18 at 23:23
M. MarzoukM. Marzouk
3911
edited Jan 1 at 1:57
M. Marzouk
asked Nov 15 '18 at 23:23
M. MarzoukM. Marzouk
3911
asked Nov 15 '18 at 23:23
M. MarzoukM. Marzouk
3911
asked Nov 15 '18 at 23:23
M. MarzoukM. Marzouk
3911
3911
1
Whether your data is sorted by ID in your source object is irrelevant. Add an ORDER BY clause to your SELECT statement and see if that makes a difference.
– Rene
Nov 15 '18 at 23:31
1
Yep, that did it. Pretty new to this. Thanks.Source = "SELECT * FROM CT_RawData WHERE [ID] BETWEEN " & StartofData & " AND " & EndofData & " ORDER BY [ID]"
– M. Marzouk
Nov 15 '18 at 23:35
Just out of curiosity - what was driving the odd sorting that was being pulled in.
– M. Marzouk
Nov 15 '18 at 23:43
I have seen this behavior too, even with just 500 lines of data.
– JonRo
Nov 15 '18 at 23:54
add a comment |
1
Whether your data is sorted by ID in your source object is irrelevant. Add an ORDER BY clause to your SELECT statement and see if that makes a difference.
– Rene
Nov 15 '18 at 23:31
1
Yep, that did it. Pretty new to this. Thanks.Source = "SELECT * FROM CT_RawData WHERE [ID] BETWEEN " & StartofData & " AND " & EndofData & " ORDER BY [ID]"
– M. Marzouk
Nov 15 '18 at 23:35
Just out of curiosity - what was driving the odd sorting that was being pulled in.
– M. Marzouk
Nov 15 '18 at 23:43
I have seen this behavior too, even with just 500 lines of data.
– JonRo
Nov 15 '18 at 23:54
1
1
Whether your data is sorted by ID in your source object is irrelevant. Add an ORDER BY clause to your SELECT statement and see if that makes a difference.
– Rene
Nov 15 '18 at 23:31
Whether your data is sorted by ID in your source object is irrelevant. Add an ORDER BY clause to your SELECT statement and see if that makes a difference.
– Rene
Nov 15 '18 at 23:31
1
1
Yep, that did it. Pretty new to this. Thanks.
Source = "SELECT * FROM CT_RawData WHERE [ID] BETWEEN " & StartofData & " AND " & EndofData & " ORDER BY [ID]"– M. Marzouk
Nov 15 '18 at 23:35
Yep, that did it. Pretty new to this. Thanks.
Source = "SELECT * FROM CT_RawData WHERE [ID] BETWEEN " & StartofData & " AND " & EndofData & " ORDER BY [ID]"– M. Marzouk
Nov 15 '18 at 23:35
Just out of curiosity - what was driving the odd sorting that was being pulled in.
– M. Marzouk
Nov 15 '18 at 23:43
Just out of curiosity - what was driving the odd sorting that was being pulled in.
– M. Marzouk
Nov 15 '18 at 23:43
I have seen this behavior too, even with just 500 lines of data.
– JonRo
Nov 15 '18 at 23:54
I have seen this behavior too, even with just 500 lines of data.
– JonRo
Nov 15 '18 at 23:54
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53329264%2fimporting-data-from-access-using-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53329264%2fimporting-data-from-access-using-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Whether your data is sorted by ID in your source object is irrelevant. Add an ORDER BY clause to your SELECT statement and see if that makes a difference.
– Rene
Nov 15 '18 at 23:31
1
Yep, that did it. Pretty new to this. Thanks.
Source = "SELECT * FROM CT_RawData WHERE [ID] BETWEEN " & StartofData & " AND " & EndofData & " ORDER BY [ID]"– M. Marzouk
Nov 15 '18 at 23:35
Just out of curiosity - what was driving the odd sorting that was being pulled in.
– M. Marzouk
Nov 15 '18 at 23:43
I have seen this behavior too, even with just 500 lines of data.
– JonRo
Nov 15 '18 at 23:54