Here is the code:
Dim cConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSql As String
sSql = "SELECT Table1.Field1,Table1.Field2, Table1.Field3 FROM Table1 WHERE Table1.Field4 = Yes"
Set cConn = New ADODB.Connection
Set rs = New ADODB.Recordset
cConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\path\files\folder\database.mdb'"
Set rs = New ADODB.Recordset
rs.Open sSql, cConn
I get the same error on the rs.Open statement every time: "Run-time Error '-2147217904 (80040e10)' No value given for one or more required parameters"
Any thoughts
Thanks,
J
VBA Excel Module opening Access DB with Recordset
Jason Lin
Hi Derek,
I have had the same error for connecting to a SQL database server from the MSDE. The problem was caused by having non English regional desktop settings (date, time, currency). After changing them to English the problem was resolved.
If you don't have a solution yet hope this helps.
Pandiarajan.net
Thank you for the help. I am still not sure why I am getting the error, but I am now confident it is a field in the connection string that is not getting set. The database is not password protected, but perhaps it thinks it should have a password
I am querying a table.
Thanks,
J
donno20
I set up a test and your code ran fine. The only difference was the database path to the connection string. I ran it from the c:\ drive. I ran another test with the database on the server using the network path and it also worked fine, any errors in the path occurs when cConn.Open is called. You connection must be ok.
Are you querying a table or a query The only thing that would cause that error is if you opened a query that required user input. I just tested my theory. I opened a query that prompts for user input and I got your runtime error.
Hope that helps
Arvin
Hi,
That was my initial thought that your connection string was off but if it were then the error would have occured when you opened the connection rather than when you used it. Check your connection string against the ones at www.connectionstrings.com and see if there is anything.
Check your table's design, check the properties of the fields and see if there is anything in there that requires a parameter. Haven't came across anything like that before but worth checking.
Hope you find the answer.