Outside contractors (1 Viewer)

[Dick7Access]

My main office access database was made by one outside contractor, and put on a server by a different one. Neither of them seem interested in helping me get the bugs out of it.
1. Where would I look to hire another tech?
2. What can we expect to pay?
3 Is there anything else the tech would need beside the password?

 

[boerbende]

Is it that bad that you can't solve it yourself with some help from the community here?

 

[Dick7Access]

Is it that bad that you can't solve it yourself with some help from the community here?

No it just seems like minor glitches, but don't I have to know SQL?

 

[boerbende]

SQL server with linked tables? You can come quite far with Internet, maybe just give it a shot.
Problem 1 = ? Let me guess. You get the message that another user had changed the same record, even when you are alone in the database
;-)

 

[Dick7Access]

SQL server with linked tables? You can come quite far with Internet, maybe just give it a shot.
Problem 1 = ? Let me guess. You get the message that another user had changed the same record, even when you are alone in the database
;-)

yes, how did you know?

 

[boerbende]

Migrated 14 databases myself, so learned it the hard way. But the problem in my situation was quickly found.
In Access I worked with Yes / No columns Or true / false.
Access can handle when the value is Null. this is similar to No / false. When migrating this info to SQL server, the value remains NULL, but SQL server is blocking the record for modification with this message that "another user ..." This message is unfortunately misleading, just not correct.

Solution in my case
1) Run a query over the yes/no columns in SQL server. When NULL , change
the value from NULL to 0
2) Make sure that whenever you create a NEW record, the value of a bit column is set to 0 (or 1, whatever is appropriate) in SQL server with the default settings

This helped in my case

 

[Dick7Access]

Migrated 14 databases myself, so learned it the hard way. But the problem in my situation was quickly found.
In Access I worked with Yes / No columns Or true / false.
Access can handle when the value is Null. this is similar to No / false. When migrating this info to SQL server, the value remains NULL, but SQL server is blocking the record for modification with this message that "another user ..." This message is unfortunately misleading, just not correct.

Solution in my case
1) Run a query over the yes/no columns in SQL server. When NULL , change
the value from NULL to 0
2) Make sure that whenever you create a NEW record, the value of a bit column is set to 0 (or 1, whatever is appropriate) in SQL server with the default settings

This helped in my case

I am sure the president of my origination will get me the password, but when I run a query in the yes/no columns don't I have to know SQL? I am pretty well along with MS Access but know nothing about SQL. I am thinking about taking a course, because I am sure they would like me to take over complete servicing of the master db. We have about 5 or 6 people in the origination that does data entry at 4 different locations but serving has to go to an outside contractor. In fact I am the only one in the org that knows access.

 

[boerbende]

I corrected first everything on SQL server directly, but I tested it now, it is possible to use the Access query builder.
And yes, I guess you absolutely need to have basic knowledge about the Access query builder and how your database is structured. If not, you can end up overwriting your data wrongly.
But you can also pull some ears now, you are one step ahead of your supplier. You can just tell him what the problem is

 

[Dick7Access]

that's the problem. They were already paid after set-up, now the fixing which was part of the contract doesn't supply anymore money so they are slow to answer. That is why I was looking to hiring my own tech, and maybe learning something along the way.

 

[Dick7Access]

I corrected first everything on SQL server directly, but I tested it now, it is possible to use the Access query builder.
And yes, I guess you absolutely need to have basic knowledge about the Access query builder and how your database is structured. If not, you can end up overwriting your data wrongly.
But you can also pull some ears now, you are one step ahead of your supplier. You can just tell him what the problem is

I just went thru a basic crash course in SQL. (30 minutes) Do I understand correctly that if I have a db I would have to re-write the whole thing over in SQL?

 

[JHB]

I just went thru a basic crash course in SQL. (30 minutes) Do I understand correctly that if I have a db I would have to re-write the whole thing over in SQL?

I think you need to clarify what you mean by that?
And it shouldn't be your problem, because you say it is already on a server.

 

[boerbende]

What I meant to say is that you could build a query in Access which changes the Null fields to 0 in the SQL server tables. This is for cleaning up the history
You need also to define the default value of the bit column in either SQL server (under default value or binding) OR in Access under properties, Default Value of each checkbox

But if you have no experience in SQL, I think it is better to ask somebody which has.
At least you have now an indication where the problem can come from.

Off course we still can help you if you want to try something yourself. I change often whole tables with a query, but I am pretty keen on having backups

 

[Dick7Access]

I think you need to clarify what you mean by that?
And it shouldn't be your problem, because you say it is already on a server.

Yes I wasn't clear on my meaning. I wasn't referring to my current situation. I am just trying to get a better understanding of SQL. From the little bit I picked up from the tutorial it seems that if I wanted to put a completely new db on a server I would have to re-write the whole thing. So my question is, is that accurate? If that is the case, then what would be the use of writing a db n access in the first place.

 

[boerbende]

Your Access is the front end. It works probably with linked tables to a SQL server database
The backend SQL server contains the tables in the database. But SQL server can host multiple databases. In my case we have one SQL server with more than 10 different databases.
I can recover an existing backuped database from yesterday as new "database" within some minutes on the same SQL server. This is just recovery, no installation necessary.
Memory and processor power can become limitations when to many users are working on one SQL server
Hope this helps you a little

 

[Dick7Access]

Your Access is the front end. It works probably with linked tables to a SQL server database
The backend SQL server contains the tables in the database. But SQL server can host multiple databases. In my case we have one SQL server with more than 10 different databases.
I can recover an existing backuped database from yesterday as new "database" within some minutes on the same SQL server. This is just recovery, no installation necessary.
Memory and processor power can become limitations when to many users are working on one SQL server
Hope this helps you a little

Thanks That is making a little more sense. Let's see if I got it right this time. (scenario) I am making a db to track item x. I make the front end with access, but the tables reside on the server which will be in SQL. Do I have it correct?

 

[boerbende]

If you make a db with Access with linked tables to SQl server, then you are correct...
When starting development in Access, the initial design will have the tables in Access. You will have to split later to frontend - backend.
Usually this split is done for security, improved performance or higher uptime, but you still can use Access as frontend and backend simultaniously

 

[Dick7Access]

If you make a db with Access with linked tables to SQl server, then you are correct...
When starting development in Access, the initial design will have the tables in Access. You will have to split later to frontend - backend.
Usually this split is done for security, improved performance or higher uptime, but you still can use Access as frontend and backend simultaniously

Once I got my organisation straighten out I had visions of doing some of my own db on a server. I think I first need to find a community college and take a course in SQL.

So I am back to square 1. Where do I go to look for a tech to help me straighten out our organisations db, how much can I expect to pay, and will he need anything beside the password?

 

[AccessBlaster]

Thanks That is making a little more sense. Let's see if I got it right this time. (scenario) I am making a db to track item x. I make the front end with access, but the tables reside on the server which will be in SQL. Do I have it correct?

Dick, either you build your database locally on your machine then split the database. After splitting on your local machine, you can export to the server via OBDC or you can choose to upsize.

Or you can use programs like Microsoft Studio or SQL express to build tables directly on the server.

If you choose to upsize or build directly on the server you will need some SQL knowledge. If you build locally and export via ODBC its easier.

You have to have permissions before proceeding.

 

[Dick7Access]

Dick, either you build your database locally on your machine then split the database. After splitting on your local machine, you can export to the server via OBDC or you can choose to upsize.

Or you can use programs like Microsoft Studio or SQL express to build tables directly on the server.

If you choose to upsize or build directly on the server you will need some SQL knowledge. If you build locally and export via ODBC its easier.

You have to have permissions before proceeding.

(Have permission) Please elaborate?

 

[Rx_]

Just and idea: Would remote terminal be an option for someone to help?
Wouldn't suggest you let just anyone join, but you have some ideas on who you might be able to trust.

Basically: Yourcomputer workstation allowed a Remote Terminal session so that someone you trust could join your session with you watching over them - and having a speakerphone chat during the session.

You would want your computer workstation to have access to SQL Server and to your Access Application Source Code.
Having someone able to remotely "drive" with you as the co-pilot would be a very efficient way to identify general situations, offer solutions, and even make changes.

Back in the late 1990's we used this to assist remote offices in the Long Term Care industry. The local techs had to be a jack of all trades. This allowed our Access / DB support staff with more specalities get involved for ten minutes to a couple of hours and solve many problems.