By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Querying SQL 2005 XPrss .mdf with Access 2000

Dököll
Expert 100+
P: 2,364
Hey Gang!

I just ran through a wonderful Microsoft video for VS 2005 Web Dev and SQL 2005 Express. Data added to SQL .mdf file through VS web site...

How does one query the data in the SQL .mdf file?

I can browse the database data through VS Web Dev application, and the database name is clearly visible when I locate the folders, but querying is far-fetched.

What do you think may be happening?

Have a great week-end!

Dököll
Jan 6 '08 #1
Share this Question
Share on Google+
16 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hey Gang!

I just ran through a wonderful Microsoft video for VS 2005 Web Dev and SQL 2005 Express. Data added to SQL .mdf file through VS web site...

How does one query the data in the SQL .mdf file?

I can browse the database data through VS Web Dev application, and the database name is clearly visible when I locate the folders, but querying is far-fetched.

What do you think may be happening?

Have a great week-end!

Dököll
Have you tried linking the tables to the Access database using an odbc link?
Jan 8 '08 #2

Dököll
Expert 100+
P: 2,364
Have you tried linking the tables to the Access database using an odbc link?
No, I have not tried that, I attempted to simply see the data by using 'Show table data' option and there were added to pane. Thought okay this could work... SQL can see Access, thus vice versa!

Looks like the ODBC connection is already created and ready, will try it. Thanks for posting this option:-)
Jan 9 '08 #3

Dököll
Expert 100+
P: 2,364
Have you tried linking the tables to the Access database using an odbc link?
I think I need to work on the ODBC part a little. Tables are invisible. I started thinking simply using ADO to see Access, Mary, do you think that might work?

Dököll
Jan 10 '08 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
I think I need to work on the ODBC part a little. Tables are invisible. I started thinking simply using ADO to see Access, Mary, do you think that might work?

Dököll
ADO will allow you to use recordsets of tables or views.
Jan 11 '08 #5

Dököll
Expert 100+
P: 2,364
ADO will allow you to use recordsets of tables or views.
Thank you thank you, I am glad I posted this, I think ADO is what I want. I needed to page through recordset, msquared...

Wait a minute...
Jan 11 '08 #6

Dököll
Expert 100+
P: 2,364
Thank you thank you, I am glad I posted this, I think ADO is what I want. I needed to page through recordset, msquared...

Wait a minute...
Hey, what is this, what'd I miss:


+++++++++++++++++++++++++++++++++++++++++
Originally Posted by mmccarthyHave you tried linking the tables to the Access database using an odbc link?


+++++++++++++++++++++++++++++++
Quote:
Originally Posted by msquaredHave you tried linking the tables to the Access database using an odbc link?

Where's Mary, What's going on? Guys!
Jan 11 '08 #7

MMcCarthy
Expert Mod 10K+
P: 14,534

Dököll
Expert 100+
P: 2,364
All depends on which version you believe ...

http://www.thescripts.com/forum/thread757266.html
http://www.thescripts.com/forum/thread757960.html
http://www.thescripts.com/forum/thread757300.html
What a beautiful way to end my night, Good heavens, Mary, sorry I missed the big bang:-)

Congratulations, Yahoo actually:-)

I'm sure he's lucky...

Dököll
Jan 11 '08 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
What a beautiful way to end my night, Good heavens, Mary, sorry I missed the big bang:-)

Congratulations, Yahoo actually:-)

I'm sure he's lucky...

Dököll
So you believe the lies.
Jan 11 '08 #10

Dököll
Expert 100+
P: 2,364
So you believe the lies.
Well I'm known to be gullable:-)

Switching channels a bit, I wanted to again thank you for mentioning ODBC, it sounds like it will be an easier way of getting data, I think the reading said something about speed. Also, I found this link thought it to be helpful, can definitely use it for this current project:

http://www.connectionstrings.com/?carrier=sqlserver2005

Must nail this connection, msquared, enjoy your week-end;-)
Jan 13 '08 #11

Dököll
Expert 100+
P: 2,364
Well I'm known to be gullable:-)

Switching channels a bit, I wanted to again thank you for mentioning ODBC, it sounds like it will be an easier way of getting data, I think the reading said something about speed. Also, I found this link thought it to be helpful, can definitely use it for this current project:

http://www.connectionstrings.com/?carrier=sqlserver2005

Must nail this connection, msquared, enjoy your week-end;-)
I think I am almost there. Of course I chickened out and attempted in VB, still no go.

Can you have a look at the VB code, see if it'd be similar to VBA?

Tried a version of it in VBA no no avail:

http://www.thescripts.com/forum/show...04#post3025504

Thanks!
Jan 13 '08 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Not sure what you are trying to do with MyRecord variable. The following is how it could be done in VBA. I think the same should work with VB.

Expand|Select|Wrap|Line Numbers
  1. Private Sub SQLServer_Click()
  2. Dim MyWorkspace As DAO.Workspace
  3. Dim MyDatabase As DAO.Database
  4. Dim rec_set As DAO.Recordset
  5. Dim MSSQL As String
  6.  
  7.     Set MyWorkspace = CreateWorkspace("", "admin", "", dbUseODBC)
  8.     Set MyDatabase = MyWorkspace.OpenDatabase("", , , "ODBC;DATABASE=FirstSQLDB")        
  9.  
  10.     MSSQL = "SELECT * FROM Email WHERE EmailAddress ='" & Text1(0).Text & "'"""
  11.     Set rec_set = MyDatabase.OpenRecordset(MSSQL)
  12.     Do While Not rec_set.EOF 'this function will keep searching for fields matching each textbox
  13.         'MsgBox ("got here")
  14.         Text1(0).Text = rec_set.Fields("EmailAddress")
  15.         Text1(1).Text = rec_set.Fields("IPAddress")
  16.         Text1(2).Text = rec_set.Fields("DateTimeStamp")
  17.         Text1(3).Text = rec_set.Fields("EmailID")
  18.         rec_set.MoveNext
  19.     Loop
  20.  
  21.     Set rec_set = Nothing
  22.     Set MyDatabase = Nothing
  23.     Set MyWorkspace = Nothing
  24.     rec_set.Close
  25.     MyDatabase.Close
  26.     MyWorkspace.Close
  27.  
  28. End Sub
  29.  
Jan 14 '08 #13

Dököll
Expert 100+
P: 2,364
Not sure what you are trying to do with MyRecord variable. The following is how it could be done in VBA. I think the same should work with VB.

Expand|Select|Wrap|Line Numbers
  1. Private Sub SQLServer_Click()
  2. Dim MyWorkspace As DAO.Workspace
  3. Dim MyDatabase As DAO.Database
  4. Dim rec_set As DAO.Recordset
  5. Dim MSSQL As String
  6.  
  7.     Set MyWorkspace = CreateWorkspace("", "admin", "", dbUseODBC)
  8.     Set MyDatabase = MyWorkspace.OpenDatabase("", , , "ODBC;DATABASE=FirstSQLDB")        
  9.  
  10.     MSSQL = "SELECT * FROM Email WHERE EmailAddress ='" & Text1(0).Text & "'"""
  11.     Set rec_set = MyDatabase.OpenRecordset(MSSQL)
  12.     Do While Not rec_set.EOF 'this function will keep searching for fields matching each textbox
  13.         'MsgBox ("got here")
  14.         Text1(0).Text = rec_set.Fields("EmailAddress")
  15.         Text1(1).Text = rec_set.Fields("IPAddress")
  16.         Text1(2).Text = rec_set.Fields("DateTimeStamp")
  17.         Text1(3).Text = rec_set.Fields("EmailID")
  18.         rec_set.MoveNext
  19.     Loop
  20.  
  21.     Set rec_set = Nothing
  22.     Set MyDatabase = Nothing
  23.     Set MyWorkspace = Nothing
  24.     rec_set.Close
  25.     MyDatabase.Close
  26.     MyWorkspace.Close
  27.  
  28. End Sub
  29.  
You are right, I no longer needed that there, that explains the error I've been getting...Thanks much msquared:-)

Will give it a whirl. What was I thinking...

In a bit!
Jan 15 '08 #14

Dököll
Expert 100+
P: 2,364
Not sure what you are trying to do with MyRecord variable. The following is how it could be done in VBA. I think the same should work with VB.

Expand|Select|Wrap|Line Numbers
  1. Private Sub SQLServer_Click()
  2. Dim MyWorkspace As DAO.Workspace
  3. Dim MyDatabase As DAO.Database
  4. Dim rec_set As DAO.Recordset
  5. Dim MSSQL As String
  6.  
  7.     Set MyWorkspace = CreateWorkspace("", "admin", "", dbUseODBC)
  8.     Set MyDatabase = MyWorkspace.OpenDatabase("", , , "ODBC;DATABASE=FirstSQLDB")        
  9.  
  10.     MSSQL = "SELECT * FROM Email WHERE EmailAddress ='" & Text1(0).Text & "'"""
  11.     Set rec_set = MyDatabase.OpenRecordset(MSSQL)
  12.     Do While Not rec_set.EOF 'this function will keep searching for fields matching each textbox
  13.         'MsgBox ("got here")
  14.         Text1(0).Text = rec_set.Fields("EmailAddress")
  15.         Text1(1).Text = rec_set.Fields("IPAddress")
  16.         Text1(2).Text = rec_set.Fields("DateTimeStamp")
  17.         Text1(3).Text = rec_set.Fields("EmailID")
  18.         rec_set.MoveNext
  19.     Loop
  20.  
  21.     Set rec_set = Nothing
  22.     Set MyDatabase = Nothing
  23.     Set MyWorkspace = Nothing
  24.     rec_set.Close
  25.     MyDatabase.Close
  26.     MyWorkspace.Close
  27.  
  28. End Sub
  29.  
Hiya, msquared!

Back to bug u. I got a pop asking for info again, I removed ODBC and added Jet to see what it would do, thus dbUseJet, glad that I did because aftteer adding the path to the SQL database:

("C:\Documents and Settings\Dököll\Submit2Site\App_Data\FirstSQLDB")

rather than ("", , , "ODBC;DATABASE=FirstSQLDB")

The file is not recognized, it's as if it does not know I need to open an SQL Database. I am still toying with it. Will let you know. Grabbed a few books at the library to help me:-)

Thanks much for coming to my aid, by the way, msquared...
Jan 15 '08 #15

Dököll
Expert 100+
P: 2,364
Hiya, msquared!

Back to bug u. I got a pop asking for info again, I removed ODBC and added Jet to see what it would do, thus dbUseJet, glad that I did because aftteer adding the path to the SQL database:

("C:\Documents and Settings\Dököll\Submit2Site\App_Data\FirstSQLDB")

rather than ("", , , "ODBC;DATABASE=FirstSQLDB")

The file is not recognized, it's as if it does not know I need to open an SQL Database. I am still toying with it. Will let you know. Grabbed a few books at the library to help me:-)

Thanks much for coming to my aid, by the way, msquared...
Hiya msquared!

I came over to report, I haven't got a clue. My wife got me some books at the Library, will muscle through and see.

Do you get the same treatment when you run the code by the way?
I agree that it should work, the SQL Express db does exist:-)

Dököll
Jan 17 '08 #16

Dököll
Expert 100+
P: 2,364
Have you tried linking the tables to the Access database using an odbc link?
I think what I am finding out is setting up a DataSource using the actual SQL server database is close to difficult. In VB or VBA and connecting to an Access database, no issues at all. In trying to complete same task with an SQL Server database, it cannot be located "connection failed"... you know the deal, msquared, surely have been there.

Now, how do I find out what my SQL Server 2005 name is, is it SQLEXPRESS. I guess I can ask in ASP, if you could fill me in, that'd be great. Where are you anyway?

Happy Friday!
Jan 18 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.