Excel - receiving run-time error 1004 ODBC Error
1
I am pretty unfamiliar with VB code and trying to solve an issue. Several users can run this macro fine, but some get a Run-time error 1004, General ODBC error. Debug points to the last line below. It is odd to me that it is setup to connect simply to a directory of a file share. Anyone know what may be going on? I have verified they have access to the share.
Option Explicit
Sub CreatePolicyTable()
Dim qryTable As QueryTable
Dim rngDestination As Range
Dim strConnection As String
Dim strSQL As String
Dim strParam1 As String
Dim strParam3 As String
Worksheets("Pol Data").Range("A1:A1048576").EntireRow.ClearContents
Call Delete_Named_Ranges
'Define the connection string and destination range.
strConnection = "ODBC;Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=\My Folder;" & _
"Extensions=asc,csv,tab,txt;"
Set rngDestination = Sheet2.Range("A1")
'Create the SQL string.
strSQL = "SELECT * FROM My_File.csv"
'set the parameter strings
strParam1 = " WHERE state = '" & Sheet5.ComboBox1.Text & "'"
strParam3 = " ;"
'check & build variable parameters depending on whether combobox ticked by user
strSQL = strSQL & strParam1 & strParam3
'Create the QueryTable.
Set qryTable = Sheet2.QueryTables.Add(strConnection, rngDestination)
'Populate the QueryTable.
qryTable.CommandText = strSQL
qryTable.CommandType = xlCmdSql
qryTable.BackgroundQuery = False
qryTable.Refresh
'Create Named Ranges
ActiveWorkbook.Names.Add Name:="PolData", RefersToR1C1:= _
"=OFFSET('Pol Data'!R1C1,0,0,COUNTA('Pol Data'!C1),COUNTA('Pol Data'!R1))"
End Sub
Sub Refresh_All()
Call CreatePolicyTable
Worksheets("Competitive Analysis").PivotTables("1").PivotCache.Refresh
Worksheets("Competitive Analysis").Activate
End Sub
Sub Delete_Named_Ranges()
Dim wbBook As Workbook
Dim nName As Name
Set wbBook = ActiveWorkbook
For Each nName In wbBook.Names
nName.Delete
Next nName
End Subexcel vba
asked Jan 19 at 3:31
Andrew OhanianAndrew Ohanian
62
add a comment |
1
I am pretty unfamiliar with VB code and trying to solve an issue. Several users can run this macro fine, but some get a Run-time error 1004, General ODBC error. Debug points to the last line below. It is odd to me that it is setup to connect simply to a directory of a file share. Anyone know what may be going on? I have verified they have access to the share.
Option Explicit
Sub CreatePolicyTable()
Dim qryTable As QueryTable
Dim rngDestination As Range
Dim strConnection As String
Dim strSQL As String
Dim strParam1 As String
Dim strParam3 As String
Worksheets("Pol Data").Range("A1:A1048576").EntireRow.ClearContents
Call Delete_Named_Ranges
'Define the connection string and destination range.
strConnection = "ODBC;Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=\My Folder;" & _
"Extensions=asc,csv,tab,txt;"
Set rngDestination = Sheet2.Range("A1")
'Create the SQL string.
strSQL = "SELECT * FROM My_File.csv"
'set the parameter strings
strParam1 = " WHERE state = '" & Sheet5.ComboBox1.Text & "'"
strParam3 = " ;"
'check & build variable parameters depending on whether combobox ticked by user
strSQL = strSQL & strParam1 & strParam3
'Create the QueryTable.
Set qryTable = Sheet2.QueryTables.Add(strConnection, rngDestination)
'Populate the QueryTable.
qryTable.CommandText = strSQL
qryTable.CommandType = xlCmdSql
qryTable.BackgroundQuery = False
qryTable.Refresh
'Create Named Ranges
ActiveWorkbook.Names.Add Name:="PolData", RefersToR1C1:= _
"=OFFSET('Pol Data'!R1C1,0,0,COUNTA('Pol Data'!C1),COUNTA('Pol Data'!R1))"
End Sub
Sub Refresh_All()
Call CreatePolicyTable
Worksheets("Competitive Analysis").PivotTables("1").PivotCache.Refresh
Worksheets("Competitive Analysis").Activate
End Sub
Sub Delete_Named_Ranges()
Dim wbBook As Workbook
Dim nName As Name
Set wbBook = ActiveWorkbook
For Each nName In wbBook.Names
nName.Delete
Next nName
End Subexcel vba
asked Jan 19 at 3:31
Andrew OhanianAndrew Ohanian
62
What is the code inDelete_Named_Ranges? You are also missingEnd Subat the end of your sub, but that would give you a different error.
– VBA Pete
Jan 19 at 7:50
Thanks Pete, I edited the code to be more complete and easier to read.
– Andrew Ohanian
Jan 19 at 15:07
add a comment |
1
1
1
I am pretty unfamiliar with VB code and trying to solve an issue. Several users can run this macro fine, but some get a Run-time error 1004, General ODBC error. Debug points to the last line below. It is odd to me that it is setup to connect simply to a directory of a file share. Anyone know what may be going on? I have verified they have access to the share.
Option Explicit
Sub CreatePolicyTable()
Dim qryTable As QueryTable
Dim rngDestination As Range
Dim strConnection As String
Dim strSQL As String
Dim strParam1 As String
Dim strParam3 As String
Worksheets("Pol Data").Range("A1:A1048576").EntireRow.ClearContents
Call Delete_Named_Ranges
'Define the connection string and destination range.
strConnection = "ODBC;Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=\My Folder;" & _
"Extensions=asc,csv,tab,txt;"
Set rngDestination = Sheet2.Range("A1")
'Create the SQL string.
strSQL = "SELECT * FROM My_File.csv"
'set the parameter strings
strParam1 = " WHERE state = '" & Sheet5.ComboBox1.Text & "'"
strParam3 = " ;"
'check & build variable parameters depending on whether combobox ticked by user
strSQL = strSQL & strParam1 & strParam3
'Create the QueryTable.
Set qryTable = Sheet2.QueryTables.Add(strConnection, rngDestination)
'Populate the QueryTable.
qryTable.CommandText = strSQL
qryTable.CommandType = xlCmdSql
qryTable.BackgroundQuery = False
qryTable.Refresh
'Create Named Ranges
ActiveWorkbook.Names.Add Name:="PolData", RefersToR1C1:= _
"=OFFSET('Pol Data'!R1C1,0,0,COUNTA('Pol Data'!C1),COUNTA('Pol Data'!R1))"
End Sub
Sub Refresh_All()
Call CreatePolicyTable
Worksheets("Competitive Analysis").PivotTables("1").PivotCache.Refresh
Worksheets("Competitive Analysis").Activate
End Sub
Sub Delete_Named_Ranges()
Dim wbBook As Workbook
Dim nName As Name
Set wbBook = ActiveWorkbook
For Each nName In wbBook.Names
nName.Delete
Next nName
End Subexcel vba
asked Jan 19 at 3:31
Andrew OhanianAndrew Ohanian
62
I am pretty unfamiliar with VB code and trying to solve an issue. Several users can run this macro fine, but some get a Run-time error 1004, General ODBC error. Debug points to the last line below. It is odd to me that it is setup to connect simply to a directory of a file share. Anyone know what may be going on? I have verified they have access to the share.
Option Explicit
Sub CreatePolicyTable()
Dim qryTable As QueryTable
Dim rngDestination As Range
Dim strConnection As String
Dim strSQL As String
Dim strParam1 As String
Dim strParam3 As String
Worksheets("Pol Data").Range("A1:A1048576").EntireRow.ClearContents
Call Delete_Named_Ranges
'Define the connection string and destination range.
strConnection = "ODBC;Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=\My Folder;" & _
"Extensions=asc,csv,tab,txt;"
Set rngDestination = Sheet2.Range("A1")
'Create the SQL string.
strSQL = "SELECT * FROM My_File.csv"
'set the parameter strings
strParam1 = " WHERE state = '" & Sheet5.ComboBox1.Text & "'"
strParam3 = " ;"
'check & build variable parameters depending on whether combobox ticked by user
strSQL = strSQL & strParam1 & strParam3
'Create the QueryTable.
Set qryTable = Sheet2.QueryTables.Add(strConnection, rngDestination)
'Populate the QueryTable.
qryTable.CommandText = strSQL
qryTable.CommandType = xlCmdSql
qryTable.BackgroundQuery = False
qryTable.Refresh
'Create Named Ranges
ActiveWorkbook.Names.Add Name:="PolData", RefersToR1C1:= _
"=OFFSET('Pol Data'!R1C1,0,0,COUNTA('Pol Data'!C1),COUNTA('Pol Data'!R1))"
End Sub
Sub Refresh_All()
Call CreatePolicyTable
Worksheets("Competitive Analysis").PivotTables("1").PivotCache.Refresh
Worksheets("Competitive Analysis").Activate
End Sub
Sub Delete_Named_Ranges()
Dim wbBook As Workbook
Dim nName As Name
Set wbBook = ActiveWorkbook
For Each nName In wbBook.Names
nName.Delete
Next nName
End Sub Option Explicit
Sub CreatePolicyTable()
Dim qryTable As QueryTable
Dim rngDestination As Range
Dim strConnection As String
Dim strSQL As String
Dim strParam1 As String
Dim strParam3 As String
Worksheets("Pol Data").Range("A1:A1048576").EntireRow.ClearContents
Call Delete_Named_Ranges
'Define the connection string and destination range.
strConnection = "ODBC;Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=\My Folder;" & _
"Extensions=asc,csv,tab,txt;"
Set rngDestination = Sheet2.Range("A1")
'Create the SQL string.
strSQL = "SELECT * FROM My_File.csv"
'set the parameter strings
strParam1 = " WHERE state = '" & Sheet5.ComboBox1.Text & "'"
strParam3 = " ;"
'check & build variable parameters depending on whether combobox ticked by user
strSQL = strSQL & strParam1 & strParam3
'Create the QueryTable.
Set qryTable = Sheet2.QueryTables.Add(strConnection, rngDestination)
'Populate the QueryTable.
qryTable.CommandText = strSQL
qryTable.CommandType = xlCmdSql
qryTable.BackgroundQuery = False
qryTable.Refresh
'Create Named Ranges
ActiveWorkbook.Names.Add Name:="PolData", RefersToR1C1:= _
"=OFFSET('Pol Data'!R1C1,0,0,COUNTA('Pol Data'!C1),COUNTA('Pol Data'!R1))"
End Sub
Sub Refresh_All()
Call CreatePolicyTable
Worksheets("Competitive Analysis").PivotTables("1").PivotCache.Refresh
Worksheets("Competitive Analysis").Activate
End Sub
Sub Delete_Named_Ranges()
Dim wbBook As Workbook
Dim nName As Name
Set wbBook = ActiveWorkbook
For Each nName In wbBook.Names
nName.Delete
Next nName
End Sub Option Explicit
Sub CreatePolicyTable()
Dim qryTable As QueryTable
Dim rngDestination As Range
Dim strConnection As String
Dim strSQL As String
Dim strParam1 As String
Dim strParam3 As String
Worksheets("Pol Data").Range("A1:A1048576").EntireRow.ClearContents
Call Delete_Named_Ranges
'Define the connection string and destination range.
strConnection = "ODBC;Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=\My Folder;" & _
"Extensions=asc,csv,tab,txt;"
Set rngDestination = Sheet2.Range("A1")
'Create the SQL string.
strSQL = "SELECT * FROM My_File.csv"
'set the parameter strings
strParam1 = " WHERE state = '" & Sheet5.ComboBox1.Text & "'"
strParam3 = " ;"
'check & build variable parameters depending on whether combobox ticked by user
strSQL = strSQL & strParam1 & strParam3
'Create the QueryTable.
Set qryTable = Sheet2.QueryTables.Add(strConnection, rngDestination)
'Populate the QueryTable.
qryTable.CommandText = strSQL
qryTable.CommandType = xlCmdSql
qryTable.BackgroundQuery = False
qryTable.Refresh
'Create Named Ranges
ActiveWorkbook.Names.Add Name:="PolData", RefersToR1C1:= _
"=OFFSET('Pol Data'!R1C1,0,0,COUNTA('Pol Data'!C1),COUNTA('Pol Data'!R1))"
End Sub
Sub Refresh_All()
Call CreatePolicyTable
Worksheets("Competitive Analysis").PivotTables("1").PivotCache.Refresh
Worksheets("Competitive Analysis").Activate
End Sub
Sub Delete_Named_Ranges()
Dim wbBook As Workbook
Dim nName As Name
Set wbBook = ActiveWorkbook
For Each nName In wbBook.Names
nName.Delete
Next nName
End Subexcel vba
excel vba
asked Jan 19 at 3:31
Andrew OhanianAndrew Ohanian
62
asked Jan 19 at 3:31
Andrew OhanianAndrew Ohanian
62
edited Jan 19 at 15:07
Andrew Ohanian
asked Jan 19 at 3:31
Andrew OhanianAndrew Ohanian
62
asked Jan 19 at 3:31
Andrew OhanianAndrew Ohanian
62
asked Jan 19 at 3:31
Andrew OhanianAndrew Ohanian
62
62
What is the code inDelete_Named_Ranges? You are also missingEnd Subat the end of your sub, but that would give you a different error.
– VBA Pete
Jan 19 at 7:50
Thanks Pete, I edited the code to be more complete and easier to read.
– Andrew Ohanian
Jan 19 at 15:07
add a comment |
What is the code inDelete_Named_Ranges? You are also missingEnd Subat the end of your sub, but that would give you a different error.
– VBA Pete
Jan 19 at 7:50
Thanks Pete, I edited the code to be more complete and easier to read.
– Andrew Ohanian
Jan 19 at 15:07
What is the code in
Delete_Named_Ranges? You are also missing End Sub at the end of your sub, but that would give you a different error.– VBA Pete
Jan 19 at 7:50
What is the code in
Delete_Named_Ranges? You are also missing End Sub at the end of your sub, but that would give you a different error.– VBA Pete
Jan 19 at 7:50
Thanks Pete, I edited the code to be more complete and easier to read.
– Andrew Ohanian
Jan 19 at 15:07
Thanks Pete, I edited the code to be more complete and easier to read.
– Andrew Ohanian
Jan 19 at 15:07
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
});
}
});
draft saved
draft discarded
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%2f54263843%2fexcel-receiving-run-time-error-1004-odbc-error%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
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f54263843%2fexcel-receiving-run-time-error-1004-odbc-error%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
What is the code in
Delete_Named_Ranges? You are also missingEnd Subat the end of your sub, but that would give you a different error.– VBA Pete
Jan 19 at 7:50
Thanks Pete, I edited the code to be more complete and easier to read.
– Andrew Ohanian
Jan 19 at 15:07