开发者

Weird situation, define a file path connecting to Access .accdb database in Excel VBA using ADO, however it tells me couldn't find .mdb file

I'm writing code in Excel VBA using ADO connection to open an Access .accdb database. I defined the file path as bellow, but when I run my code, error message tells me that "couldn't find file C:\Users\sevenice\Documents\EM Database.mdb". It's so weird that开发者_Python百科 I never define that file path which in .mdb file extent.

However, when I save accdb file as mdb file and then place the mdb file under Documents folder, the code works! But I couldn't figure it out why.

Is there anything wrong with my codes? Or there exists some default file path in Access 2007? My code is in Excel 2007.

Thanks, Bing

Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset

Dim Dbfilepath As String

Dbfilepath = "C:\Users\sevenice\Desktop\EM Database.accdb"

Set cnn = New ADODB.Connection

cnn.Open "Provider= Microsoft.ACE.OLEDB.12.0;" & " Data Source=" & Dbfilepath & ";" & "Persist Security Info =False;"

Set rst = New ADODB.Recordset
rst.ActiveConnection = cnn


Are you referencing the database name in your SQL text (which you have snipped from your code as posted)?

e.g.

SELECT [g/gtop] FROM [EM Database].[N (t) Data]

AFAIK this syntax will attempt to find a database EM Database.mdb in the current directory. I guess it uses .mdb to be compatible with legacy code (or it is a bug! :)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜