Needs to be an easier way (1 Viewer)

AC5FF · Oct 30, 2014

I really enjoy databases and figuring them out; but these SQL Databases are kicking my backside! :banghead: :banghead:

There must be an easier way! If I could only easily (and would have to be done daily) move data from an SQL database into an Access database I would be set.

I have tried writing queries on my data and those work; but getting them into classes or whatever is just ridiculous. Then I need to 'join' all these classes to extract the two or three lines of data that are relevant to what I need to find. If this were in Access I would have been done in the time it took me to write this post.

Any suggestions???

GBalcom · Oct 30, 2014

I'm going through learning and implementing an access FE with an SQL BE myself. but, once it's linked in access, I can read/write any data desired easily with access. I can pull recordsets through access vba easily, just need to link as a table.

The only thing I can't do is change the design of the database, I'm using SQL server management studio for that.

I've found youtube pretty helpful so far, but I still have ALOT to learn!

AC5FF · Oct 30, 2014

I've not seen a FE Access with BE SQL before... Might have to look at that.

Rx_ · Oct 30, 2014

Likewise, I have DSN-Less connections to SQL Server using SQL Server Native Client 11.0
There are several post I have made on this. Then there are some excellent post by others on this site.

Then by using some code, queries, and coffee, I automate creating Access databases. For example, matching up some regulatory information and call the GIS layer to evaluate specific needs. The code deletes the last Access DB at a network location, then creates a spanking new complete Access DB to replace it with all of the data. It seems that I posted something about that too.

My suggestion would be to break down the top level reqirements of what you are trying to accomplish. This creates a master plan with categories for programming each step.

AC5FF · Oct 30, 2014

I've sort of already broke down what I need and would like to accomplish.

To set the record straight - I mispoke on my first post. This is not a true SQL Database (as I have come to understand). It's a SQL Server Compact Edition File - basically a *.SDF file.

Using MS VB 2010 Express I have looked at the tables/structure/etc. I know that I'm only needing data from two or three tables, and out of those tables I am only needing maybe 3 or 4 columns of data. If I can get something to work that will extract just that data so it is usable to me in Access then my problems will be solved.

Galaxiom · Oct 31, 2014

Connect the tables to Access using External Data > ODBC

Use New DSN > SQL Server when asked by the Wizard

AC5FF · Oct 31, 2014

Galaxiom

I had such high hopes! Saw your post last night and wanted to try this morning. After getting to the New DSN and choosing SQL Server I was ready to make this work!

Then - the balloon deflates! HAHA! I browse to the location of the .SDF file and it's not shown - so not selectable.

I am going to retry this method a few times and hope. I'll read through the New DSN list of options and try a few. This 'Almost' did it!!

Keeping my fingers crossed another option will work....

AC5FF · Oct 31, 2014

Nope...... No Go with that option...

AC5FF · Oct 31, 2014

I keep reading that linking or accessing SQL Server files in Access is easy and the steps seem straight forward.

However, a SQL Server Compact Edition seems to be different. I've yet to figure out exactly what that difference is and/or why one will work but not the other. Hope someone can enlighten me here because that is something I cannot find while searching the web.

SQL_Hell · Oct 31, 2014

does this help?

http://stackoverflow.com/questions/...pact-edition-tables-relationships-with-micros

AC5FF · Oct 31, 2014

SQL_Hell

Thanks for that link. It's actually one I got excited about when I found it the other day. However, me being the complete n00b with VB I was not 100% sure about everything I was reading.

The answer here starts by saying you can link the table from SQL CE directly into Access - which is what I would love to do. However I cannot get that to work - Access does not recognize the SDF format file. From what I've been able to read (IF I understand things correctly) is Access can link to an ODBC format/?? but not a OELDB. I've yet to completely understand the difference between these, but have learned that OELDB is what the SQL CE uses.

Now, I'm unsure about the code he lists too. Can this be inserted into Access to make things work? Although I am pretty good with Access this area (again VB) is my downfall.

The code listed on the site is:

Code:

Sub test()
Dim pConn As ADODB.Connection
Dim pRS As ADODB.Recordset
Set pConn = New ADODB.Connection
Dim cmd As New ADODB.Command
Set pRS = New ADODB.Recordset
' For 3.0 use PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0
pConn.ConnectionString = 
"PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Northwind.sdf"
pConn.Open

cmd.ActiveConnection = pConn
cmd.CommandText = "SELECT * FROM Products"
Set pRS = cmd.Execute
' Open the recordset
While Not pRS.EOF
    Debug.Print pRS(0)
    Debug.Print pRS(1)
    pRS.MoveNext
Wend
End Sub

If this can be inserted into Access great!!! But can someone maybe walk me through how that would be done??

Thanks everyone!!! I am hopeful this can be resolved!

Galaxiom · Nov 1, 2014

AC5FF said:
I browse to the location of the .SDF file and it's not shown - so not selectable.

The DSN connects to the server then you choose the database you want to connect to. You cannot connect to the data file.

On database servers the engine is separate from the data. You must have SQL Server Express installed. It should be listed in the available servers while setting up the DSN.

At no point in this process do you get involved with data files. They are connected to the engine by the server when the database is set up on the server. All dealing with the data is via the server.

AC5FF · Nov 3, 2014

Galaxiom

Okay - I think I'm understanding you. I need to set up an SQL Server first; then with my SDF file linked/used in that configuration I should be able to link the tables into Access.

Since I don't have a server set up - I need to create one. I thought I had the program installed but I guess not? Back to the Google Page I go!

Needs to be an easier way (1 Viewer)

AC5FF

Registered User.

GBalcom

Much to learn!

AC5FF

Registered User.

Rx_

Nothing In Moderation

AC5FF

Registered User.

Galaxiom

Super Moderator

AC5FF

Registered User.

AC5FF

Registered User.

AC5FF

Registered User.

SQL_Hell

SQL Server DBA

AC5FF

Registered User.

Galaxiom

Super Moderator

AC5FF

Registered User.

Similar threads

Users who are viewing this thread